Extracting Word form Data and exporting to Excel spreadsheet

[camdameron]

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

[macropod]

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.

[camdameron]

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

[macropod]

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
    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 

VBA tags courtesy of www.thecodenet.com

[dinozgb]

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!

[macropod]

I have no idea what you mean by:

Quote: 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.

[Aviaf]

Edit--

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

[TimH]

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.

[TimH]

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.

[CatonElMenor]

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!

[CatonElMenor]

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.

[CatonElMenor]

Sorry about the previous posts.

This is the file.

[CatonElMenor]

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

[macropod]

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