Transfer Excel data to MS Word Text FormField VBA Code
Results 1 to 2 of 2

Thread: Transfer Excel data to MS Word Text FormField VBA Code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transfer Excel data to MS Word Text FormField VBA Code

    Hello, I recently wrote a VBA code that inputs excel data into a word document through the use of bookmarks. The code works great, but I would actually like to use a text FormField. My question is do I basically need to scrap my whole code or can I simply replace the "item" code with a reference to the FormField Text? Thanks in advance for any insight. My code is below:


    Code:
    Sub Test() 
     Dim WDApp As Word.Application
     Dim myDoc As Word.Document
     Dim mywdRange As Word.Range
     Dim r As Long
     Dim m As Long
     
     On Error GoTo errorHandler
    
    
     Set WDApp = New Word.Application
     With WDApp
     .Visible = True
     .WindowState = wdWindowStateMaximize
     End With
     
     With Sheets("Sheet1")
             m = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    
    For r = 3 To m
             Set myDoc = WDApp.Documents.Add(Template:="C:Desktop\Test.docm")
             With myDoc.Bookmarks
                 .Item("EOD").Range.InsertBefore Sheets("Sheet1").Range("A" & r)
                 .Item("IED").Range.InsertBefore Sheets("Sheet1").Range("B" & r)
                 .Item("FED").Range.InsertBefore Sheets("Sheet1").Range("C" & r)
                 .Item("IP").Range.InsertBefore Sheets("Sheet1").Range("D" & r)
                 .Item("MN").Range.InsertAfter Sheets("Sheet1").Range("E" & r)
                 .Item("MName").Range.InsertBefore Sheets("Sheet1").Range("F" & r)
                 .Item("LOCA").Range.InsertBefore Sheets("Sheet1").Range("G" & r)
                 .Item("NOB").Range.InsertBefore Sheets("Sheet1").Range("H" & r)
                 .Item("BOC").Range.InsertBefore Sheets("Sheet1").Range("I" & r)
                 .Item("Add").Range.InsertBefore Sheets("Sheet1").Range("J" & r)
             End With
    
    
             
             myDoc.SaveAs2 Filename:="C:Desktop\Test" & Sheets("Sheet1").Range("H" & r) & ".docx", _
                 FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
             
         Next r
      
    errorHandler:
         Set WDApp = Nothing
         Set myDoc = Nothing
         Set mywdRange = Nothing
     End Sub
    Last edited by Fluff; Jul 9th, 2019 at 09:52 AM. Reason: Code tags

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Transfer Excel data to MS Word Text FormField VBA Code

    Once you've inserted your text form fields (legacy forms) in the document, with the same names as the bookmarks, you would have to replace the With myDoc.Bookmarks ... End With block with something like:

    Code:
            myDoc.FormFields("EOD").Result = Sheets("Sheet1").Range("A" & r)
            myDoc.FormFields("IED").Result = Sheets("Sheet1").Range("B" & r)
            myDoc.FormFields("FED").Result = Sheets("Sheet1").Range("C" & r)
            'etc.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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