Transfer Excel data to MS Word Text FormField VBA Code

asgreek

New Member
Joined
Apr 24, 2019
Messages
2
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 a moderator:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,322
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,453
Messages
5,486,992
Members
407,575
Latest member
calc

This Week's Hot Topics

Top