Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Populate Word Form fields using excel data

    Hello,

    I am trying to populate word forms using excel data. I was successful in populating single row for one form field (using bookmark) using below code. But what I need is populate multiple rows of data from excel to single bookmark. can we do this?

    Sub PopulateWordDocFromExcel()


    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wdBmRng As Word.Range

    Dim bWeStartedWord As Boolean
    Dim i As Integer

    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    bWeStartedWord = True
    End If
    wrdApp.Visible = True

    Set wrdDoc = wrdApp.Documents.Open("....\Form.docx")

    With wrdDoc

    On Error Resume Next
    Set wdBmRng = wrdDoc.Bookmarks("ID").Range
    On Error GoTo 0
    If Not wdBmRng Is Nothing Then
    wdBmRng.Text = Range("C4:C21").Value
    Else
    MsgBox "Bookmark not found!"
    Stop
    End If
    wrdApp.DisplayAlerts = wdAlertsNone
    .SaveAs "...\Form.docx", FileFormat:=12
    .Close ' close the document
    wrdApp.DisplayAlerts = wdAlertsAll
    End With

    ExitPoint:
    If bWeStartedWord Then wrdApp.Quit 'close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    End Sub

    Thanks in advance
    Last edited by gade.vani; 2015-04-30 at 14:09.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    gade.vano,

    This amended code will place the values from multiple Excel rows from the range C4 to C21 into the bookmark "ID" within the Word document.

    HTH,
    Maud

    Code:
    Sub PopulateWordDocFromExcel()
    Application.ScreenUpdating = False
    On Error Resume Next
    '-----------------------------------
    'DECLARE AND SET VARIABLES
        Dim wrdApp As Word.Application
        Dim wrdDoc As Word.Document
        Dim wdBmRng As Word.Range
        Dim bWeStartedWord As Boolean
        Dim I As Integer
        Set wrdApp = GetObject(, "Word.Application")
    '-----------------------------------
    'START WORD
        If wrdApp Is Nothing Then
        Set wrdApp = CreateObject("Word.Application")
        bWeStartedWord = True
        End If
    '-----------------------------------
    'OPEN DOCOUMENT
        wrdApp.Visible = True
        Set wrdDoc = wrdApp.Documents.Open("....\Form.docx")
    '-----------------------------------
    'POPULATE BOOKMARK
        With wrdDoc
        Set wdBmRng = wrdDoc.Bookmarks("ID").Range
        If Not wdBmRng Is Nothing Then
            wdBmRng.Text = StringMe(Range("C4:C21"))
        Else
            MsgBox "Bookmark not found!"
            GoTo ExitPoint
        End If
    '-----------------------------------
    'SAVE FILE AND CLOSE
        wrdApp.DisplayAlerts = wdAlertsNone
        .SaveAs "....\Form.docx", FileFormat:=12
    ExitPoint:
        .Close ' close the document
        wrdApp.DisplayAlerts = wdAlertsAll
        End With
    '-----------------------------------
    'CLEANUP
        If bWeStartedWord Then wrdApp.Quit 'close the Word application
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    
    Function StringMe(rng As Range) As String
    Dim cell As Range
    StringMe = ""
    For Each cell In rng
         If StringMe = "" Then
            StringMe = cell.Text
         Else:
            StringMe = StringMe & Chr(13) & cell.Text
         End If
    Next cell
    End Function
    Note: A reference to Microsoft Word 14.0 Object Library must be made for the OP's and the amended code to work
    Last edited by Maudibe; 2015-05-02 at 09:57. Reason: added note

Posting Permissions

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