CODEWITHSUNDEEP
excelvbams-word
a_js12
a_js12

Reputation: 329

VBA: Excel Data to MS Word FormField

I'm trying to copy data from an Excel document to a Word document. My excel doc has 4 columns and I've set my word document to have 4 formfields named after the Excel column names. When I run my code I get the following error: Run time error '91': Object variable or With block variable not set. The line where I'm erroring out is highlighted with ** **.

In my private sub I defined and set my variable wdFormField so I'm confused why I'm getting this error. Any ideas?

Option Explicit


Sub Checklist()
Dim WDApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim r As Long
Dim m As Long
Dim dDate As Date
Dim strNumber As String

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
If Range("A" & r).EntireRow.Hidden = False Then

Set myDoc = WDApp.Documents.Add(Template:="X:\abcde.docx\")
Copy_Cell_To_Form_Field myDoc, Range("A" & r).Value, "Text1"
Copy_Cell_To_Form_Field myDoc, Range("B" & r).Value, "Text2"
Copy_Cell_To_Form_Field myDoc, Range("C" & r).Value, "Text3"
Copy_Cell_To_Form_Field myDoc, Range("D" & r).Value, "Text4"

myDoc.SaveAs2 Filename:="X:\abcde.docx", _
FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
myDoc.Close
End If
Next r

End Sub
Private Sub Copy_Cell_To_Form_Field(doc As Word.Document, cellValue As Variant, formFieldName As String)

    Dim i As Integer
    Dim wdFormField As Word.FormField

    
    Set wdFormField = Nothing
    i = 1
    ' the next line gives me: Run time error '91': Object variable or With block variable not set
    While i <= doc.FormFields.Count And wdFormField Is Nothing
        If doc.FormFields(i).Name = formFieldName Then Set wdFormField = doc.FormFields(i)
        i = i + 1
    Wend
    
    If Not wdFormField Is Nothing Then
        wdFormField.Result = cellValue
    Else
        MsgBox "Form field bookmark " & formFieldName & " doesn't exist in " & doc.Name
    End If
    
End Sub

Upvotes: 0

Views: 385

Answers (1)

macropod
macropod

Reputation: 13505

There are multiple errors in your code, including:

  • X:\abcde.docx\ is not a valid filename. Moreover, a docx file isn't really a template.
  • Having defined wdFormField As Word.FormField you can't set it to Nothing.

Try the following:

Sub Checklist()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdRng As Word.Range
Dim xlSht As Worksheet, r As Long, c As Long
Set xlSht = Sheets("Sheet1")
With wdApp
  .Visible = True
  For r = 3 To xlSht.Range("A" & xlSht.Rows.Count).End(xlUp).Row
    If xlSht.Range("A" & r).EntireRow.Hidden = False Then
      Set wdDoc = wdApp.Documents.Add(Template:="X:\abcde.docx")
      With wdDoc
        For c = 1 To 4
          If .Bookmarks.Exists("Text" & c) Then
            .Bookmarks("Text" & c).Range.Fields(1).Result.Text = xlSht.Cells(r, c).Text
          Else
            MsgBox "Form field bookmark 'Text" & c & "' doesn't exist in:" & vbCr & _
              .AttachedTemplate.FullName
          End If
        Next
        .SaveAs2 Filename:="X:\abcde.docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .Close
      End With
    End If
  Next r
  .Quit
End With
End Sub

Upvotes: 1

Related Questions