Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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
    Last edited by RetiredGeek; 2016-04-05 at 20:11. Reason: Added Code Tags

  2. #2
    Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    68
    Thanks
    0
    Thanked 24 Times in 24 Posts
    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
    Graham Mayor - Word MVP
    http://www.gmayor.com

  3. The Following User Says Thank You to Graham Mayor For This Useful Post:

    luai94 (2016-04-06)

  4. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Graham Mayor View Post
    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.
    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?
    Last edited by macropod; 2016-04-06 at 03:09. Reason: Repaired QUOTE tagging

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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/show...l=1#post257696 & http://www.vbaexpress.com/forum/show...l=1#post291047
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. The Following User Says Thank You to macropod For This Useful Post:

    luai94 (2016-04-06)

  7. #5
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  8. #6
    Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    68
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by luai94 View Post
    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.
    Graham Mayor - Word MVP
    http://www.gmayor.com

  9. #7
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Graham Mayor View Post
    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).

  10. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by luai94 View Post
    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
    [MS MVP - Word]

  11. #9
    Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    68
    Thanks
    0
    Thanked 24 Times in 24 Posts
    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.
    Graham Mayor - Word MVP
    http://www.gmayor.com

  12. #10
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    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. Document.PNG

  13. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  14. #12
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    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?
    Attached Files Attached Files

  15. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  16. #14
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    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?

  17. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    For a generic macro that extracts data from formfields and content controls alike, see: http://www.msofficeforums.com/word/2...html#post87355
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •