Transfer Excel data to MS Word Text FormField VBA Code

asgreek

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top