Unanswered: Show only required data and transfer to MS Word
I have a spreadsheet that shows some materials we use in our company. The first column shows a description of the material, the second column shows the catalogue number of the material and the third column is left open for the user to enter in the required quantity.
I have attached to the spreadsheet a command button but I need some help with the VB coding. What I'd like to do is have the user enter the required quantities in the 3rd column and then click on this command button which would then only show the rows that have had quantities entered. If I could get this done it would be sufficient.
However if it is possible I would then like to have these rows tranfer into an order form which i already have developed in MS Word.
I think this will give a a pointer, but I am not too familiar with Word ojects and methods so you will need to investigate opening specific files and entering text (and formating) at specific bookmarks etc., as you require Pehaps someone else will expand on this.
Dim WordApp As Word.Application
Dim Doc As Word.Document
Dim DescCol As Long
Dim QuantCol As Long
Dim TopRow As Long
Dim BotRow As Long
Dim i As Long
Dim j As Long
DescCol = 1 ' You decide ?
QuantCol = 3 ' You decide ?
TopRow = 2 ' You decide?
BotRow = ActiveCell.Row
Set WordApp = New Word.Application
Set Doc = WordApp.Documents.Add
For i = TopRow To BotRow
If Not IsNumeric(Cells(i, QuantCol)) Or Cells(i, QuantCol) = 0 Then
Rows(i).Hidden = True
Dim WordText As String
WordText = ""
For j = DescCol To QuantCol
WordText = WordText & " " & Cells(i, j) & vbTab
.TypeText Text:=Mid(WordText, 2) ' Mid removes leading 2 spaces
WordApp.Visible = True
You will neet to set e reference to Microsoft Word Oject Library for the above code to work (or use GetObject or Create/Object).