Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005

    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.

    Any help with this would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    Hiding Rows and Copying to 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.

    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
        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
                Next j
                With WordApp.Selection
                    .TypeText Text:=Mid(WordText, 2)  ' Mid removes leading 2 spaces
                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).



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts