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

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

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.

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

[RP196]

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

[sam53]

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.

[macropod]

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.

[Buccaneer66]

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

[macropod]

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.

[gravenrj]

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.

[gravenrj]

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