Run Time Error ’91’: Object variable or With block variable (VBA)

Topic

New Reply

hello everyone,

i dont have much knowledge in VBA, so i found a code that could help me with the work i am doing which is exporting data from word and putting in Excel. So i copied the code as explained in the tutorial but it keeps giving me this error. I will post the code and the tutorial Video if anyone can help me, it would be much appreciated.
https://www.youtube.com/watch?v=1x-Vk4Qmpz0

Code:

Sub GetFormData()
 Application.ScreenUpdating = False
 Dim wdApp As New Word.Application
 Dim wdDoc As Word.Document
 Dim FmFld As Word.FormField
 Dim strFolder As String, strFile As String
 Dim WkSht As Worksheet, i As Long, j As Long
 strFolder = GetFolder
 If strFolder = "" Then Exit Sub
 Set WkSht = ActiveSheet
 i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
 
 strFile = Dir(strFolder & "*.docx", vbNormal)
 While strFiled  ""
    i = i + 1
    Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
         j = 0
         For Each FmFld In .FormFields
             j = j + 1
             WkSht.Cells(i, j) = FmFld.Result
         Next
       End With
       wdDoc.Close SaveChanges:=False
       strFiled = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: SetWkSht = Nothing
    Application.ScreenUpdating = True
End Sub

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

Reply | Quote

Replies

If you use Option Explicit at the top of the module, then compile the code, you will see a number of errors in the code – the most glaring being strFiled , when the variable is strFile. Also SetWkSht = Nothing should be Set WkSht = Nothing. If Word is running, it is faster to get the running Word version.

Code:

Option Explicit

Sub GetFormData()
    Application.ScreenUpdating = False
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim FmFld As Object
    Dim strFolder As String, strFile As String
    Dim WkSht As Worksheet, i As Long, j As Long
    strFolder = GetFolder
    If strFolder = “” Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row

    On Error Resume Next
    Set wdApp = GetObject(, “Word.Application”)
    If Err Then
        Set wdApp = CreateObject(“Word.Application”)
    End If
    On Error GoTo 0

    strFile = Dir(strFolder & “*.docx”, vbNormal)
    While strFile  “”
        Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & “” & strFile, AddToRecentFiles:=False, Visible:=False)
        With wdDoc
            If .formfields.Count > 0 Then
                i = i + 1
                j = 0
                For Each FmFld In .formfields
                    j = j + 1
                    WkSht.Cells(i, j) = FmFld.Result
                Next
            End If
        End With
        wdDoc.Close SaveChanges:=False
        strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
End Sub

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

Reply | Quote

If you use Option Explicit at the top of the module, then compile the code, you will see a number of errors in the code – the most glaring being strFiled , when the variable is strFile. Also SetWkSht = Nothing should be Set WkSht = Nothing. If Word is running, it is faster to get the running Word version.[/quote]

Thank you for your help there Graham, i made the adjustments and it stopped giving me any errors but it is still not giving me the data I want (for this simple example). And what do you mean get the running Word version?

Reply | Quote

Thank you for your help there Graham, i made the adjustments and it stopped giving me any errors but it is still not giving me the data I want (for this simple example). And what do you mean get the running Word version?

The code does what it is supposed to i.e. read the form fields from the documents into a worksheet. As for the ‘running version’. The original is run from Excel and creates a new instance of Word. Nothing wrong with that, but if Word is already open, you can use instead the open version.

Reply | Quote

The code does what it is supposed to i.e. read the form fields from the documents into a worksheet. As for the ‘running version’. The original is run from Excel and creates a new instance of Word. Nothing wrong with that, but if Word is already open, you can use instead the open version.

In the youtube video I linked, when you press run and pick the folder, it reads the information in the word document and write it in the Excel Sheet, but mine is not doing that (I made a similar Word file to try it out).

Reply | Quote

Don’t you just love it when people steal your code to make a ‘tutorial’, attributing its origins to a question on an unspecified ‘webinar’ the day before!!! For the original, which has nothing to do with any ‘webinar’, see: http://www.vbaexpress.com/forum/showthread/?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=257696&viewfull=1#post257696 & http://www.vbaexpress.com/forum/showthread/?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=291047&viewfull=1#post291047

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Don’t you just love it when people steal your code to make a ‘tutorial’, attributing its origins to a question on an unspecified ‘webinar’ the day before!!! For the original, which has nothing to do with any ‘webinar’, see: http://www.vbaexpress.com/forum/showthread/?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=257696&viewfull=1#post257696 & http://www.vbaexpress.com/forum/showthread/?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=291047&viewfull=1#post291047

Hello Macropod,

I am so sorry that your original code was stolen and used for other purposes! but I thank you for response and your efforts on this code.

I have another question, if it possible I am using this code to try to get an understanding of how to import data from word to excel but the problem is that the Word document that I want to get information from is very complicated, the info I need is in the fourth page and its like a table with multiple choices answers. Is there a way to make a code that would go to a specific line that I want and take the crossed answer from the three choices (I cant post the Word document because of company rules).

If you have any insight about this, it would be very helpful

Reply | Quote

Hello Macropod,

I am so sorry that your original code was stolen and used for other purposes! but I thank you for response and your efforts on this code.

I have another question, if it possible I am using this code to try to get an understanding of how to import data from word to excel but the problem is that the Word document that I want to get information from is very complicated, the info I need is in the fourth page and its like a table with multiple choices answers. Is there a way to make a code that would go to a specific line that I want and take the crossed answer from the three choices (I cant post the Word document because of company rules).

If you have any insight about this, it would be very helpful

Reply | Quote

I am using this code to try to get an understanding of how to import data from word to excel but the problem is that the Word document that I want to get information from is very complicated, the info I need is in the fourth page and its like a table with multiple choices answers. Is there a way to make a code that would go to a specific line that I want and take the crossed answer from the three choices

The code is designed specifically for collecting data from formfields, not for collecting data from table cells. So, unless your data are in formfields in your table, nothing will be collected. The original code at vbaexpress was designed to do the same thing, except with content controls, if that’s what you’re using. Otherwise we’d need more specific information about your table (e.g. which table in the document it is, which rows & columns the data are to be collected from, etc.).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

The code is designed specifically for collecting data from formfields, not for collecting data from table cells. So, unless your data are in formfields in your table, nothing will be collected. The original code at vbaexpress was designed to do the same thing, except with content controls, if that’s what you’re using. Otherwise we’d need more specific information about your table (e.g. which table in the document it is, which rows & columns the data are to be collected from, etc.).

Okay, I was allowed to give some information by my company, which hopefully it would give you guys a more specific information of what I need.
I divided the two important things that I would need from the document, so part one is part of a table lets say row 2, column 1 and 2, page 7 of the document.
So I need the code to find which answer was crossed in this part of the table (attached you will find an image of what I mean) and put that answer in the excel sheet.

For part 2, I need it to find the special risks part of the document and if something is written under it, then it would give me description of this risk and the level of it. 44092-Document

Reply | Quote

The macro reads legacy text form fields. Do your documents contain legacy form fields? It will not read content controls.
If you have content controls or a mixture of form fields types, take a look at http://www.gmayor.com/ExtractDataFromForms.htm which is made harder for our plagiarist video making friend to use the code unattributed.

P.S. It seems Paul was answering while I was typing. I can only echo his comments.

Reply | Quote

I’m sorry, but an image of part of a page the document is no use at all when it comes to analysing the content; an actual page from the document is needed.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I’m sorry, but an image of part of a page the document is no use at all when it comes to analysing the content; an actual page from the document is needed.

Is that maybe a little better to help you? or still that word document is not enough?

Reply | Quote

You have three checkbox content controls for ‘size’ and another three for ‘level’. Capturing the checkbox states is easy-enough, but having your present arrangement leaves you open to having more than one checkbox in each group checked. I’d suggest using two dropdowns instead, one for ‘size’ and another for ‘level’ with the three choices in each. That way only a single item can be chosen. Plus, it means there are only two content controls to collect data from.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

You have three checkbox content controls for ‘size’ and another three for ‘level’. Capturing the checkbox states is easy-enough, but having your present arrangement leaves you open to having more than one checkbox in each group checked. I’d suggest using two dropdowns instead, one for ‘size’ and another for ‘level’ with the three choices in each. That way only a single item can be chosen. Plus, it means there are only two content controls to collect data from.

The Problem is that its a Standard template which i can’t Change on it. Which makes me obliged to use it the way it is done but i will ask if we can Change the template to two Dropdowns (if that makes things easier).
So what is the way to capture the Checkbox state? is there an easy code for it?

Reply | Quote

For a generic macro that extracts data from formfields and content controls alike, see: http://www.msofficeforums.com/word/27552-extracting-data-word-documents.html#post87355

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

thank you for that code, but the major problem now is how do i make the code go to a specific table/field and take that data only, because i dont want it to take everything.

Reply | Quote

Simply insert:
.Tables(1).Range
before:
.Formfields
and:
.ContentControls
where 1 is the table #.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

i put that command : .Tables(1).Range and as u said i put it before formfields and contentcontrols but it keeps giving me an error,
which says Invalid use of property. What could be the problem i am doing?

Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & “” & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
.Tables(1).Range
For Each FmFld In .FormFields
j = j + 1
With FmFld
Select Case .Type
Case Is = wdFieldFormCheckBox
WkSht.Cells(i, j) = .CheckBox.Value
Case Else
WkSht.Cells(i, j) = .Result
End Select
End With
Next
For Each CCtrl In .ContentControls
j = j + 1
With CCtrl
Select Case .Type
Case Is = wdContentControlCheckBox
WkSht.Cells(i, j) = .Checked
Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
WkSht.Cells(i, j) = .Range.Text
Case Else
End Select

Reply | Quote

When I said

Simply insert:
.Tables(1).Range
before:
.Formfields
and:
.ContentControls
where 1 is the table #.

I meant it literally, as in:
For Each FmFld In .Tables(1).Range.Formfields
and:
For Each CCtrl In .Tables(1).Range.ContentControls

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote