My guess is that you have not set a reference to the Word object library. The following doesn't need such a reference and does work, albeit with reservations about the fact that there is no error handling whatsoever to ensure that the documents processed all match, or that they even contain content controls or form fields. If there happens to be a document in the folder that doesn't match the parameters it is likely to crash. I would also caution against checking form fields and content controls in the same process. The two are not really compatible with one another. I have also replaced the getfolder function as it is a pain to navigate with a large folder structure.
Option Explicit Sub GetFormData() 'Note: this code DOES NOT require a reference to the Word object model Dim wdApp As Object Dim wdDoc As Object Dim CCtrl As Object Dim FmFld As Object Dim strFolder As String, strFile As String Dim WkSht As Worksheet, i As Long, j As Long Application.ScreenUpdating = False strFolder = BrowseForFolder("Select folder containing the form documents") If strFolder = "" Then Exit Sub Set WkSht = ActiveSheet WkSht.Rows("2:" & Rows.Count).ClearContents i = 1 strFile = Dir(strFolder & "\*.docx", vbNormal) On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 While strFile <> "" i = i + 1 Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & strFile, AddToRecentFiles:=False, Visible:=False) With wdDoc j = 0 For Each CCtrl In .ContentControls j = j + 1 WkSht.Cells(i, j) = CCtrl.Range.Text Next For Each FmFld In .FormFields j = j + 1 WkSht.Cells(i, j) = FmFld.Result Next End With wdDoc.Close 0 strFile = Dir() DoEvents Wend wdApp.Quit Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing Application.ScreenUpdating = True End Sub Private Function BrowseForFolder(Optional strTitle As String) As String 'Graham Mayor 'strTitle is the title of the dialog box Dim fDialog As FileDialog On Error GoTo err_Handler Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = strTitle .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show <> -1 Then GoTo err_Handler: BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92) End With lbl_Exit: Exit Function err_Handler: BrowseForFolder = vbNullString Resume lbl_Exit End Function




Reply With Quote