Forum Discussion
Excel macro to import to a Fillable Form in Word
- Jul 07, 2023
I figured it out, so if anyone else needs this code in the future, hopefully they can find this. This will insert text from Excel into a fillable form on Word without deleting the fillable blocks. I attached excel and word documents for users to sample and pasted the macro below:
Option Explicit
Sub ImportValuesToWord()
Dim wdApp As Object
Dim wdDoc As Object
Dim templatePath As String
Dim bookmarkName As String
Dim cellValue As Variant
Dim nm As Name ' Declare nm as a variable of type Name
' Prompt the user to select the Word document to import values into
With Application.FileDialog(3) ' 3 represents msoFileDialogFilePicker
.Title = "Select the Word document to import values into"
.Filters.Clear
.Filters.Add "Word Documents", "*.docx; *.docm"
If .Show = -1 Then
templatePath = .SelectedItems(1)
Else
Exit Sub ' User canceled the document selection
End If
End With
' Open Word and the selected document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application") ' Attempt to get an existing instance of Word
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = CreateObject("Word.Application") ' If no existing instance found, create a new one
End If
' Open the original protected document
Set wdDoc = wdApp.Documents.Open(templatePath, ReadOnly:=False)
' Unprotect the Word document
wdDoc.Unprotect Password:=""
' Loop through all defined names in the workbook
For Each nm In ThisWorkbook.Names
bookmarkName = nm.Name
' Check if the bookmark exists in the Word document
If wdDoc.Bookmarks.Exists(bookmarkName) Then
' Retrieve the cell value from Excel based on the named range
cellValue = nm.RefersToRange.Value
' Insert the cell value into the Word document
InsertText wdDoc, bookmarkName, CStr(cellValue), False
End If
Next nm
' Protect the Word document again
wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
' Save and close the Word document
wdDoc.SaveAs2 templatePath ' Save the document without creating copies
wdDoc.Close
' Quit Word application
wdApp.Quit
' Release object references
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub
Sub InsertText(doc As Object, ByVal bookmarkName As String, ByVal bookmarkText As String, showMsg As Boolean)
On Error GoTo locerr
Dim rng As Object
Dim fld As ObjectOn Error Resume Next
Set rng = doc.Bookmarks(bookmarkName).RangeIf rng Is Nothing Then
If showMsg And Len(Trim(bookmarkText)) > 0 And bookmarkName <> "author" Then
MsgBox "The bookmark named:" _
& vbNewLine & vbNewLine & bookmarkName & vbNewLine & vbNewLine & _
"Is absent from the current document.", vbOKOnly, "Error message"
End If
Else
On Error GoTo locerr
If Len(bookmarkText) = 0 Then
bookmarkText = bookmarkText & " "
End If' If there is a FormField at the bookmark, update its Result property
If rng.FormFields.Count > 0 Then
For Each fld In rng.FormFields
fld.Result = bookmarkText
Next fld
Else
' If no FormField, insert the text normally and reapply the bookmark
rng.Text = bookmarkText
doc.Bookmarks.Add bookmarkName, rng
End If
End IfTidyUp:
Exit Sub
locerr:
If MsgBox(Err.Description & ", " & Err.Number, vbAbortRetryIgnore) = vbRetry Then
Resume
Else
Resume TidyUp
End If
End Sub
JKPieterse sorry for the lat response. I put together a general example spreadsheet and word document as per the attached. I am getting this error when trying to export the values: "The range cannot be deleted., 6028". Do you have any idea what might be causing this?
Dexter_G I'm afraid I'm offline for the time being worth no access to a pc
- Dexter_GJun 29, 2023Copper ContributorJKPieterse have you gained access to a computer yet?