Hello,


I wrote some code to pull content controlled fields in word, search the title of the field in the header of an excel table, identify the column number, determine the next row in that column, and paste the value of the field.


The code actually seems to work fine except when it tries to loop in the next word doc in the specified folder.


here's the entire module. 

'This code pulls word documents with Content Controls and inserts them into a spreadsheet for analysis
Sub getformdata()
'Prevents screen from refreshing during Macro to make it more efficient
Application.ScreenUpdating = False
'
'Set up variables
Dim wdapp As New Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.ContentControl
Dim strFolder As String
Dim strFile As String
Dim WkSht As Worksheet
Dim i As Long
Dim j As Long


'Defines "strfolder" as the appropriate folder from which you want to pull your word documents from
'It does so using the "GetFolder()" separate function below
strFolder = GetFolder
'If there is nothing in the specified folder, this line tells the macro to stop
If strFolder = "" Then Exit Sub
'
'
'Set data to dump on sheet number one regardless of what sheet in the work book you are on
Set WkSht = Worksheets(1)
'Sets "strFile" as the name of the first word doc in the chosen folder path
strFile = Dir(strFolder & "\*.doc", vbNormal)
'The below while loop grabs a word doc in the specified folder, pastes all the Content Controlled fields of a word doc in a specified row/column based on the column header in row 1
    While strFile <> ""
        Set wdDoc = wdapp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False, ReadOnly:=True)
        With wdDoc
            'This loop grabs each form field from a word doc and places it in its specified "ith" row and "jth" collumn
            'Every row is the same for each document, while every collumn is the consequent field form.
            For Each CCtrl In .ContentControls
                With CCtrl
                    Select Case .Type
                            Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
                                j = Application.WorksheetFunction.Match(CCtrl.Title, Range("1:1"), 0)
                                i = WkSht.Cells(WkSht.Rows.Count, j).End(xlUp).Row
                                i = i + 1
                                WkSht.Cells(i, j).Value = .Range.Text
                            Case Else
                    End Select
                End With
            Next
            
        wdDoc.Close SaveChanges:=False
        strFile = Dir()
        wdapp.Quit
    Set wdDoc = Nothing: Set wdapp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
        End With
    Wend
End Sub


' The following function is called for use in the above Sub getformdata()
'
'The function opens up a box to select the folder for which all the word docs in question should be located
'Do not select a folder that does not contain word docs with form fields


Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
End Function
The piece that is giving me trouble is

For Each CCtrl In .ContentControls
                With CCtrl
                    Select Case .Type
                            Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
                                j = Application.WorksheetFunction.Match(CCtrl.Title, Range("1:1"), 0)
                                i = WkSht.Cells(WkSht.Rows.Count, j).End(xlUp).Row
                                i = i + 1
                                WkSht.Cells(i, j).Value = .Range.Text
                            Case Else
                    End Select
                End With
            Next

I am getting a "Run time error '91' Object variable or with block variable not set" on the line "i = WkSht.Cells(WkSht.Rows.Count, j).End(xlUp).Row" ONLY on the second loop.


If anyone has any insight or resources on this problem it would be much appreciated.


Thanks!