locked
Trying to Create a Macro...it's not going well RRS feed

  • Question

  • Hello! 

    I am trying to create a macro for this Questionnaire I am building. I need it to pull information from 3 or so different tables in word (all in same doc) and export them into Excel. I have fields that gather personal info like Name, Address, Telephone Number, Email, etc, but also fields that take info like Tax Filing State and Tax ID number. There will be about 40 fields in all when the questionnaire is done. Each field is being filled in using a legacy tool- text form field. Is there some sort of macro shell I can use that I can fill in with each field when I have completed it? I have office 2013, not sure if that matters.

    Thank you!

    Wednesday, June 11, 2014 12:28 PM

Answers

  • The following Excel macro will pull the data from all formfields in all Word files in a specified folder into the active worksheet. To run it, simply press Alt-F8 and choose the 'GetFormData' macro. You'll be asked to navigate to the source folder. Once you've done that, the macro will populate the worksheet. The first row will be left alone, so you can populate it with headings before or after running the macro.

    Sub GetFormData()
         'Note: this code requires a reference to the Word object model
        Application.ScreenUpdating = False
        Dim wdApp As New Word.Application
        Dim wdDoc As Word.Document
        'Dim CCtrl As Word.ContentControl
        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 & "\*.doc", vbNormal)
        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 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

    Note the commented-out code that allows the macro to work with content controls as well or instead.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, June 11, 2014 1:04 PM

All replies

  • The following Excel macro will pull the data from all formfields in all Word files in a specified folder into the active worksheet. To run it, simply press Alt-F8 and choose the 'GetFormData' macro. You'll be asked to navigate to the source folder. Once you've done that, the macro will populate the worksheet. The first row will be left alone, so you can populate it with headings before or after running the macro.

    Sub GetFormData()
         'Note: this code requires a reference to the Word object model
        Application.ScreenUpdating = False
        Dim wdApp As New Word.Application
        Dim wdDoc As Word.Document
        'Dim CCtrl As Word.ContentControl
        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 & "\*.doc", vbNormal)
        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 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

    Note the commented-out code that allows the macro to work with content controls as well or instead.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, June 11, 2014 1:04 PM
  • Thank you so much for your help! One question (I've never learned coding before so this is all brand new to me...):

    When you say "this code requires a reference to the Word object model", what exactly does that mean? I've been getting that same error in the code I've been playing around with but I can't figure out how to fix it...

    Thanks!

    Wednesday, June 11, 2014 1:13 PM
  • When you say "this code requires a reference to the Word object model", what exactly does that mean? I've been getting that same error in the code I've been playing around with but I can't figure out how to fix it...


    In the Excel VBE, go to Tools|References. Scroll down till you see 'Microsoft Word 15.0 Object Library' and check it.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, June 11, 2014 10:54 PM
  • Hi,

    Sorry to hijack this post but this code is great except that my forms were saved as .docm Macro enabled format documents.  I tried changing the "strFile = Dir(strFolder & "\*.doc", vbNormal) " to "strFile = Dir(strFolder & "\*.docm", vbNormal) " but assume I need to change another bit to, could you help perhaps?

    Thank you.

    Monday, February 8, 2016 2:55 PM
  • The macro, as coded, will work equally well with .doc, .docx and .docm files. Changing:
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    to:
    strFile = Dir(strFolder & "\*.docm", vbNormal)
    only means that the macro won't open .doc or .docx files.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, February 8, 2016 9:26 PM
  • Hello, and thanks for your help that worked perfectly!  One more question if possible, is there a way where even an empty content control will populate a cell so each row isn't all out of whack if someone doesn't answer one of the questions?

    Thanks,

    Sam

    Thursday, March 10, 2016 11:56 AM
  • It already does that. The only thing that would compromise the output is if someone deleted a content control or formfield.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, March 10, 2016 12:13 PM
  • Hi Paul. When I try to run the macro I get the following error:

        Compile error:

        User type not defined

    This is referring to the following line:

        Dim wdDoc As Word.Document

    However, I can't add the Microsoft Word Object Library reference, as I get the next error:

        Error in loading DLL.

    Is there a way to fix this?

    Friday, April 7, 2017 5:07 PM
  • Have you tried repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair)?

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, April 7, 2017 11:52 PM
  • When I try to add references - Microsoft Word 14 Object Library

    I get the error message... "Can't perform requested operation"

    Not sure why but I can't work out how to add in the library.

    What do I need to do next?





    • Edited by Reeninoz Friday, September 29, 2017 3:20 AM
    Friday, September 29, 2017 3:13 AM
  • The Microsoft Word 14 Object Library reference would only apply if you're using Office 2010; if you're using a different version, you need to add the reference appropriate to that version. If you can't add the appropriate reference and Word is installed and you're getting "Can't perform requested operation", try repairing the Office installation (e.g. via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, September 29, 2017 3:23 AM
  • Hi, I just updated everything 5 min before I posted. That was what my microsoft chat help said to do last night. I am also on a Mac and use the 365 version of Word and Excel, not sure if that matters.
    Friday, September 29, 2017 3:43 AM
  • Macs have no repair function; if Office is faulty, a complete uninstall/reinstall of Office would be required. That said, I doubt the macro would work on a Mac anyway, as it's written for a Windows environment; Macs use different path separators and the GetFolder function is Windows-specific. I don't know enough of the Mac system to write code for that.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, September 29, 2017 4:05 AM
  • That make sense. If anyone knows how to use this code with a Mac let me know.

    Thanks!

    Friday, September 29, 2017 4:24 PM
  • Hi Paul, 

    This has been very helpful. I am just stuck on the last part. I run the macro but nothing populates in the fields and I don't get any errors. Could you please help me to figure out what could be off?

    Any help is greatly appreciated!


    Wednesday, January 24, 2018 6:34 PM
  • Do the documents in the source folder actually contain formfields or content controls? If content controls, did you uncomment the relevant code lines?

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, January 24, 2018 10:23 PM
  • I hadn't put the content controls in... Thanks so much!
    Thursday, January 25, 2018 3:51 PM