Excel Hints

Results 1 to 17 of 17

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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    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
    [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
    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.
    VB:
    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 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 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 
            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 
    
    
    Formatting tags added by mark007
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    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
    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
    [MS MVP - Word]

  7. #7
    Edit--

    Sorry, was going to post an issue, but found solution in another thread

  8. #8
    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.

  9. #9
    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.

  10. #10
    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!

  11. #11

    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.

  12. #12

    Sorry

    Sorry about the previous posts.

    This is the file.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  13. #13

    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

  14. #14
    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
    to:
    For Each FmFld In .FormFields

    Change:
    WkSht.Cells(i, j) = CCtrl.Range.Text
    to:
    WkSht.Cells(i, j) = FmFld.Result
    Last edited by macropod; 05-22-2013 at 09:45 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  15. #15
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  16. #16
    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.

  17. #17
    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
    [MS MVP - Word]

Posting Permissions

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