Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 30

Thread: Extracting Word form Data and exporting to Excel spreadsheet

  1. #1

    Extracting Word form Data and exporting to Excel spreadsheet

    Hi there,
    I know there is another thread similar to this one but I could not see the attached file to view the code and I was hoping someone might be able to walk me through this.
    I have a Word form (attached) with various data (drop downs, date boxes, simple text fields and text fields where paragraphs are entered).
    Basically I just need a few fields from these extracted to an excel spreadsheet and I'm unsure how this works. Do i press a button (like I'm hitting save" once I'm done entering the data? Or does the spreadsheet extract the info somehow?

    Ideally, the column headers will be tracking number, date received, date of event, Customer Name, etc. across the columns, so that we can filter on all customers or all dates received, etc. and the tracking numbers will be sequential.

    Can someone get me started? I'd really appreciate a shove in the right direction.

    Thanks,
    Cameron
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Hi candameron,

    Assuming you have an Excel workbook ready for the data to be input (ie with the tracking number, date received, date of event, Customer Name, etc. across the columns), there are various approaches that can be taken for populating it. Which you use depends pretty much on how the data are being collected.

    If, for example, all the users doing the data entry are using the same network/PC, you might add code to the Word document to export the data directly to Excel once the data-entry has been completed. This might be triggered by the user closing the form after all the fields have been completed.

    Conversely, if the users aren't all on the same system, you would probably want some code in the Excel workbook instead, to pull in the data from all the files found in a particular folder. The code in Excel might be triggered by opening the workbook, or by the user pressing Alt-F8 to access the macro, or via a keystroke combination or a button or userform placed in a strategic position.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    OK, that is exactly what I need.
    I think the button on the spreadsheet side of things would work best.
    How do I go about doing that? I have attached the form, please let me know what I need to do from here.
    Do I set up the spreadsheet with the appropriate headers and create a button?
    I appreciate all your help!

    Cameron

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Hi candameron,

    The following Excel macro will pull the data from all content controls 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. If you want, you could create a keyboard shortcut or a button to trigger the macro.

    If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
    Sub GetFormData()
    'Note: this code requires a reference to the Word object model
    'To do this, go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
    Dim strFolder As String, strFile As String, 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 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
          With CCtrl
            Select Case .Type
              Case Is = wdContentControlCheckBox
               j = j + 1
               WkSht.Cells(i, j).Value = .Checked
              Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
               j = j + 1
               WkSht.Cells(i, j).Value = .Range.Text
              Case Else
            End Select
          End With
        Next
        .Close SaveChanges:=False
      End With
      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
    Last edited by macropod; 07-11-2022 at 02:35 PM. Reason: Replace defunct VBA tags with CODE tags
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Newbie
    Joined
    Sep 2012
    Posts
    5
    Location
    Hi!
    My problem is similar to the word file, with the need to transfer data to an Excel spreadsheet, at the end of each meeting of the document!
    Can you help me and suggest a macro for Word that after closing the document, directly transferred data in an Excel spreadsheet, taking into account the previous record!
    Thanks and regards!

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I have no idea what you mean by:
    at the end of each meeting of the document
    In any event, the above macro is for extracting forms data from multiple files and saving the data in the Excel workbook, all in one go.

    So, instead of hijacking a dormant existing thread, please start a new thread stating clearly what your needs are.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Hi,

    I'm new to much of this so I simply copied your code into my Excel file and got the following error at 'Dim wdApp As New Word.Application'.

    Compile error:
    User-defined type not defined

    I'd guess its related to the comment 'Note: this code requires a reference to the Word object model' but in what way please?

    Regards,

    Tim.

  8. #8
    Hi,

    Found the answer on another thread so for anyone who has the same problem.....

    You need to 'enable' (excuse any misunderstanding there please!) the reference to the Word object from within the VBA window following Tools > References > Microsoft Word Object Library

    Thanks,

    Tim.

  9. #9
    Hi there! Im new in this. I tryed the macro. Pasted it in VB and enabled Word 12 Objects in Tools. When executing the macro, the sand clock appears for a few seconds, like it is working, and then nothing. No error messagge and no exportation done. Can anyone tell me what im doing wrong?
    I have excel 2007. The word form has 10 active fields (I dont know if thats the exact name) and has free text also.

    Thanks!

  10. #10

    Question

    Hello! Im new in this, so probably im making the dumbest mistake.
    I pasted the VB code in Excel in the GENERAL tab. I enables the Word 12 Object libraries. I have 20 Word archives (all forms filled with patients information) in a Directory. I run the macro. Looks like its working but, without error messagge, nothing gets done.
    What am I doing wrong?

    I send you a sample form as an attachment.

    Thanks.

  11. #11

    Sorry

    Sorry about the previous posts.

    This is the file.
    Attached Files Attached Files

  12. #12

    Me again

    I think I found the problem. My form was made with formfields in Word 2003 and not with Content Controls. I made a dummy form with Word 2007 and the macro works great.

    How can I make this macro work for my Word 2003 form with Form Fields instead of Content controls?

    Thanks

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Change:
    Dim CCtrl As Word.ContentControl
    to:
    Dim FmFld As Word.FormField

    Change:
    strFile = Dir(strFolder & "\*.docx", vbNormal)
    to:
    strFile = Dir(strFolder & "\*.doc", vbNormal)

    Change:
    For Each CCtrl In .ContentControls
    …
    Next
    to:
        For Each FmFld In ActiveDocument.FormFields
          j = j + 1
          With FmFld
            Select Case .Type
              Case Is = wdFieldFormCheckBox
               WkSht.Cells(i, j).Value = .Checked
              Case Else
               WkSht.Cells(i, j).Value = .Result
              Case Else
            End Select
          End With
        Next
    Last edited by macropod; 09-06-2018 at 07:21 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  14. #14
    VBAX Newbie
    Joined
    May 2014
    Posts
    1
    Location
    Hi,

    I am using office 2011 for mac, the forms that I wish to extract are as attached, about 50 of them all in one folder. I am sure that I have got this to work in the past, trying again today I cannot get it to work.

    When I use macropod's VB above, I get a runtime error saying that Activex cannot create an object, and debug takes me to the line:
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)

    I'd appreciate any advice "for dummies", even things like where to put correct paths etc.

    Thanks!

    Ruaraidh
    Attached Files Attached Files

  15. #15
    VBAX Newbie
    Joined
    Sep 2014
    Posts
    1
    Location
    Hi there,
    Could the macro be adjusted to extract both Content Controls and Form Fields? The form I have requires the drop down menus to be Content Controls (as there are more than 25 selections in a number of instances), and I need to set a character limit for text fields so need to use Form fields for text entries.

    Thanks.

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Of course it could! That's just a matter of combining the code from posts #4 & #14. It's up to you to decide which you want to extract first - the Content Controls or the Formfields - you can't easily extract them in order if they're interspersed.

    However, you should not be using Content Controls and Formfields in the same document anyway - they're not designed to work together and doing so can cause problems in the document.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #17
    Hi

    I'm very new to this and have tried the code & get the same error.

    Compile error:
    User-defined type not defined

    I tried this solution "
    You need to 'enable' (excuse any misunderstanding there please!) the reference to the Word object from within the VBA window following Tools > References > Microsoft Word Object Library"

    But References is greyed out in my VBA tools menu, can some one help please?

    Thanks

    Chris

  18. #18
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Buccaneer66 View Post
    I tried this solution "You need to 'enable' (excuse any misunderstanding there please!) the reference to the Word object from within the VBA window following Tools > References > Microsoft Word Object Library"

    That 'solution' is nothing more than a re-hash of a comment in the macro itself:
    'Note: this code requires a reference to the Word object model
    But References is greyed out in my VBA tools menu, can some one help please?

    That will be because you haven't cleared the error. Click on Run>Reset, then set the reference.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  19. #19
    This macro is brilliant macropod (Paul)! Is there an easy way to modify the code to choose an individual file rather than a file folder? I see that you used the shell.BrowseForFolder method in the UDF, is there an equivalent browing for files? I apologize if this is a basic question, I am a VBA neophyte and for the life of me cannot find an easy way to select and store an individual file path if the file is stored in a folder with other files.

  20. #20
    Quote Originally Posted by gravenrj View Post
    This macro is brilliant macropod (Paul)! Is there an easy way to modify the code to choose an individual file rather than a file folder? I see that you used the shell.BrowseForFolder method in the UDF, is there an equivalent browing for files? I apologize if this is a basic question, I am a VBA neophyte and for the life of me cannot find an easy way to select and store an individual file path if the file is stored in a folder with other files.
    Nevermind, I was able to update this on my own by modifying the code that macropod provided and using Application.GetOpenFileName. I realize this isn't pretty and the while loop is redundant for the single file, but hey, it works! I have this as part of a userform, hence the private sub. Here it is:

    Private Sub File_Button_Click()
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim CCtrl As Word.ContentControl
    Dim strFile As String
    Dim WkSht As Worksheet, i As Long, j As Long

    strFile = Application.GetOpenFilename
    If strFile = "" Or strFile = "False" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    While strFile <> ""
    i = i + 1
    Set wdDoc = wdApp.Documents.Open(Filename:=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
    End With
    wdDoc.Close SaveChanges:=False
    strFile = ""
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 'T
    Application.ScreenUpdating = True
    Call Cancel_Click
    End Sub

Posting Permissions

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