Consulting

Results 1 to 12 of 12

Thread: Building Word Documents based on Individual Excel Rows

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Building Word Documents based on Individual Excel Rows

    Hello,
    Im seeking help ... I have a VB script that passes information from the first row of an excel document to a word document.

    The first row has a "Create Document" button in the 6th cell ("F" column)

    On every row there will be a "create document" button that will transfer
    the information from that that row to a word document, but only the information in that row.

    Im wondering what I would add to my script so that when the "Create Document" button is clicked for that row - the script only builds a word document based on the information in that specific row.
    (Any help is appreciated!!)

    Here is my code:
    Option Explicit
    Public Sub TransferData()
        ' This macro transfers the data range "A1:E1" to a table in Word
        ' Constants:
        ' docFullName = The full name of an already existing Word document
        ' Variables:
        ' doc = The Word document (assumed to be empty)
        ' i = A counter (for rows)
        ' j = A counter (for columns)
        ' tbl = A Word table
        ' wdRng = A Word range (the first paragraph of doc)
        ' wks = The worksheet "data" that contains the data range
        ' Const docFullName = "Automation\Word.doc" '
        Dim doc As Object
        Dim i As Long
        Dim j As Long
        Dim tbl As Object
        Dim wdApp As Object 'Only if you require a new document each time
        Dim wdRng As Object
        Dim wks As Worksheet
        ' Assing Word objects 'Only if you require a new document each time
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        Set doc = wdApp.Documents.Add
        ' Assign variables and objects
        ' Set doc = GetObject(docFullName) 'Only if you want a specific document
        Set wdRng = doc.Paragraphs(1).Range
        Set tbl = doc.Tables.Add(wdRng, 11, 5)
        Set wks = ThisWorkbook.Worksheets("Transmittal")
        ' Transfer the data
        With tbl
            For i = 1 To 1
                For j = 1 To 5
                    .Cell(i, j) = wks.Cells(i, j)
                Next j
            Next i
        End With
        ' Save and close doc 'Only if you want a specific document
        ' Call doc.Save
        ' Call doc.Close(False)
        ' Clean
        Set doc = Nothing
        Set wks = Nothing
    End Sub
    
    Private Sub CommandButton2_Click()
    End Sub
    Right now, this will build a document based on row 1 in the Excel document but
    when someone add's information in Row 2, then what? I can add another form > button > but what do I add into my script so that when this button on row2 is clicked it only builds a word document based on the information in row 2
    Last edited by Aussiebear; 07-09-2025 at 03:02 PM.

Posting Permissions

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