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,323
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,706
Messages
5,488,423
Members
407,638
Latest member
brandynl

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top