Show only required data and transfer to MS Word

Neil_Pattison · #1 (**permalink**) 11-25-05, 06:23

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.

Any help with this would be greatly appreciated.

MikeTheBike · #2 (**permalink**) 11-25-05, 09:45

Hi

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.

Code:

Sub HideAndCopyToWord()
    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?
    
    Cells(65536, DescCol).Select
    Selection.End(xlUp).Select
    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
        Else
            Dim WordText As String
            WordText = ""
            For j = DescCol To QuantCol
                WordText = WordText & "  " & Cells(i, j) & vbTab
            Next j
            
            With WordApp.Selection
                '.TypeParagraph
                .TypeText Text:=Mid(WordText, 2)  ' Mid removes leading 2 spaces
                .TypeParagraph
            End With
        End If
        
    Next i
    
    WordApp.Visible = True
End Sub

You will neet to set e reference to Microsoft Word Oject Library for the above code to work (or use GetObject or Create/Object).

HTH

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech