Solved: Excel macro to extract Word form data into Excel

[Humphrey]

Hello all,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every sixth field starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you.
____________________________

8680

[Humphrey]

Maybe this will be helpful to everyone ..

Create a folder on your desktop called <macro> and a folder therein called <process>.
Place <sample.doc> into that <process> folder.
Place <sample.xls> into the <macro> folder if you like.
Look at the macro code in <sample.xls> and change <User> to <your desktop user name> (2 places).

This <sample.xls> (hopefully in next reply) has a macro that saves the word form data in <sample.doc> to one row in the spreadsheet. (The code also allows you to save more docs to rows under it which for me is not necessary, but ok anyway). You can see in the spreadsheet how I would prefer it if possible, five columns.

Thanks, I hope that makes it clearer to everyone.
________________________________________

8685
.

[Humphrey]

Here is the spreadsheet ..

8686

Thank you.

[Humphrey]

Sorry everyone.
Due to a message on this board "To be able to post links your post count must be 5 or greater. Your post count is 3 momentarily", I have a couple more posts here to make before I can insert hyperlinks.

[Humphrey]

Yet another post here .. la de dah

[Humphrey]

Out of courtesy, I should inform you that I have also posted the question in these forums ..

http://www.excelforum.com/excel-programming-vba-macros/855809-excel-macro-to-extract-word-form-data-into-excel.html?p=2909165#post2909165

http://forums.techguy.org/business-applications/1066617-excel-macro-extract-word-form.html#post8451764

I will advise here as soon as a solution to my post is reached, Thanks.

[Humphrey]

Closing the post in 2-3 days (if I can), a little time in case someone is pondering it.
Thank you to all those who took the time to look at it and try it for me. No doubt there were a few who did and I appreciate it.
Stay safe.

[macropod]

Your plethora of posts, especially those suggesting closure, don't contribute to a solution. Indeed, unless one reads the thread, they give the impression you might already be receiving help.

Given that your sample has groups of five formfields, this specification:

but with every sixth field starting a new row in excel
doesn't make any sense at all, especially in light of:

You can see in the spreadsheet how I would prefer it if possible, five columns.
That said, assuming you're working with groups of five formfields, try something along the lines of:
Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:="Path & Filename", AddToRecentFiles:=False)
With wdDoc
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int(i / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

[Humphrey]

Not a nibble to date. You get to think that there is no possibility of a solution and just want to tidy up and 'close shop'.
I'll try the code, thanks.
Phil

[Humphrey]

Wow, Paul, that is amazing! I am speechless but I can still type!
Works perfectly for such a compact piece of code. I can easily change the number of columns to receive the fields also. It even adds to a previous pass and leaves a row space between.

[Humphrey]

I like to set 'Format Cells' from General to Text in the sheet (excel 2003), so that a number like 0001 in the field comes in that way, and not as '1'. I wonder how I can get rid of the ugly little cell marker that tells me I have a number set as text. Not that it really matters, I can just ignore it.

[Humphrey]

My children will wonder why I am so excited this Fathers Day, than any other previously.
Have yourself a really nice day too.
Thank you so-o-o-o-o much.
Phil
P.S. Now I have enough posts to send you a private message. Sorry to do it this way.

[macropod]

Please read what I said before about your 'plethora of posts'.

As you now appear to have the solution you sought, you should advise all the other forums of that fact, with a link to the solution.

Please also mark this thread as 'solved'.

[Humphrey]

Yes, I have already done that.
I have now noticed though in your code, that the last column is all down by one cell in level.
Can that resolve somehow?

[macropod]

Oops! Change 'Int(i / 5) ' to 'Int(i - 1 / 5)'.

PS: You can also delete ', j As Long' - it's not used.

[Humphrey]

I did post at the following forum from 17 - 26 August:
http://forums.techguy.org/business-applications/1065471-solved-exporting-repeated-forms-one.html#post8443219

When it got too messy (my fault) and I thought the question needed a fresh start, I closed it giving the resaon why and reposted it in a different way on the same forum:
http://forums.techguy.org/business-applications/1066617-solved-excel-macro-extract-word.html#post8451764

Since I then felt that the question was maybe getting too hard to resolve, I posted here and a couple of other forums in a final effort on the following day. I did think that it would be polite to advise you and others that I was now looking elsewhere and I advised the location of my posts in those forums, see above for instance. I also said in those forums that I would advise everyone when a solution was 'found'.

To be quite honest, I recently did not think there was going to be a solution at all, since it has been 2 weeks now since I first posted the question. That is why I was preparing to close on all forums. I know you probably didn't know all those details and I hope you now understand.

[Humphrey]

With those changes, each of the 5 columns is lower by one cell, like steps down, and one cell per row.

[macropod]

Damn - missing brackets. Try 'Int((i - 1) / 5)'

Not wanting to be picky, but:

it has been 2 weeks now since I first posted the question
the first post I saw (yesterday) was the the one you started this thread with, on 27 August. That's only 6 days ago and the earliest in any of the links you posted is only 7 days ago, so I don't know where the 2 weeks comes from.

[Humphrey]

Damn those brackets, hey.
Very good Paul, thanks again. All complete.
Cheers,
Phil

[Bill70]

Hello Macropod,
I am new to macro and I am using you code to extract data from a word document. But, "wdApp As New Word.Application" is highlighted" and I get the following error: "User-defined type not defined"
Any idea what the problem is?

I am using MS Office Pro 2010




Your plethora of posts, especially those suggesting closure, don't contribute to a solution. Indeed, unless one reads the thread, they give the impression you might already be receiving help.

Given that your sample has groups of five formfields, this specification:

doesn't make any sense at all, especially in light of:

That said, assuming you're working with groups of five formfields, try something along the lines of:
Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:="Path & Filename", AddToRecentFiles:=False)
With wdDoc
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int(i / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

[Bill70]

Macropod,

I was able to figure out the problem, but now the excelsheet doesn't load the data in the word doc. The word doc appears for few seconds and closes.


Hello Macropod,
I am new to macro and I am using you code to extract data from a word document. But, "wdApp As New Word.Application" is highlighted" and I get the following error: "User-defined type not defined"
Any idea what the problem is?

I am using MS Office Pro 2010

[macropod]

The macro is for running from Excel. In Excel's vbe you need to set a reference to the Word Object library, which you do via Tools|References.

As for the document, the macro opens it, reads in the formfield data, then closes the document. There is no reason to keep it open.

[Bill70]

Thanks for the reply.
The other issue is that it doesn't pull the data from the word document.
Any suggestion?

The macro is for running from Excel. In Excel's vbe you need to set a reference to the Word Object library, which you do via Tools|References.

As for the document, the macro opens it, reads in the formfield data, then closes the document. There is no reason to keep it open.

[macropod]

Have you replaced "Path & Filename" with the real Path & Filename for your document? Does your document actually contain any formfields?

[Bill70]

Yes, I replaced "Path & Filename" with the actual path and filename.
No, the ducument doesn't contain any form field


Have you replaced "Path & Filename" with the real Path & Filename for your document? Does your document actually contain any formfields?

[Tommy]

@Bill70
You need to add a reference to the Microsoft word library

[Bill70]

Thank you, but that was done already. The problem now is that the macro doesn't pull data from my word document.

[macropod]

No, the ducument doesn't contain any form field
So why are you using this macro? All of the discussion in this thread has concerned the extraction of formfield data. No more, no less.

[Bill70]

Oops, I guess I misread the original post.
I actually need a macro that will pull data from a word document.

[Bill70]

Any help will be appreciated.

[macropod]

In that case, find some code in a different thread that does what you're after* or, if you can't find one, start a new thread setting out exactly what it is you need to do - saying something as vague as "I actually need a macro that will pull data from a word document" won't do. You need to say exactly how a macro is to identify that data.

* See, for example, http://www.vbaexpress.com/forum/showthread.php?t=42850 (http://www.vbaexpress.com/forum/showthread.php?t=42850). You may be able to tweak the code there to suit your needs

[Bill70]

Thanks macropod,
I will look at the example.

[jmdeland]

Sorry for bumping an old thread but this seems to do close to what I am looking for with only a couple changes.

Instead of using only one file, I am trying to get a file dialog box to open allowing me to choose which word file I want to extract data from.

My document template has 8-10 content control text fields but I only want to import the data from 4 of them (tagged: Mar, Jun, Sept, Dec). The data from each content control text should go into its own cell starting at whichever cell is selected and going across the row.

I posted this question in a new thread here although in a much more confusing manner. I found this thread during a search on another board.

[macropod]

You could try something like:
Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, StrFlNm As String
With Application.Dialogs(xlDialogFindFile)
SendKeys "%n *.doc ~"
If .Show = True Then
StrFlNm = .Name
Else
Exit Sub
End If
End With
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
With wdDoc
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

[jmdeland]

I was getting the 400 error so I added some code to describe the error I am getting and it is a "file format is not valid" error. This occurs after I select the document I want to open. Any ideas of what I might change?

Edit:
The code I added was:

On Error GoTo Errorcatch

etc.

exit sub

Errorcatch:
MsgBox Err.Description

[macropod]

Sorry, I chose the wrong dialogue. Change:
With Application.Dialogs(xlDialogFindFile)
to:
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
and change:
StrFlNm = .Name
to:
StrFlNm = .SelectedItems(1)

[jmdeland]

The code is running fine now, the document I choose is opening and closing however no text is being transferred to the excel spreadsheet. I assume I would need to define the tags for the fields I wish to copy but I am not sure where to do that.

Thanks for all your help so far!!


Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, StrFlNm As String
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
SendKeys "%n *.doc ~"
If .Show = True Then
StrFlNm = .SelectedItems(1)
Else
Exit Sub
End If
End With
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
With wdDoc
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

[macropod]

On re-reading your initial post, I see that you are using content controls, whereas as explained previously (most recently in post #28), the code is for extracting data from formfields.

For your purposes, you will need to change:
For i = 1 To .FormFields.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .FormFields(i).Result
Next
to:
For i = 1 To .ContentControls.Count
xlWkSht.Cells(lRow + Int((i - 1) / 5), ((i - 1) Mod 5) + 1).Value = .ContentControls(i).Range.Text
Next

[jmdeland]

This is SUPER close, the only change I'm looking for is that the current code grabs the input from every content control in the document. I wish to only choose a few of the content controls to gather and keep them on the same row in the sheet.

These content control tags will never change (I will always only want the info from content control text tagged (Mar, Jun, Sept, Dec)) whereas the document has multiple untagged content control text that the user fills in which for my report are irrelevant.

I can't thank you enough for getting me this far!

[macropod]

Try something along the lines of:
For i = 1 To .ContentControls.Count
Select Case .ContentControls(i).Tag
Case "Mar": xlWkSht.Cells(lRow, 1).Value = .ContentControls(i).Range.Text
Case "Jun": xlWkSht.Cells(lRow, 2).Value = .ContentControls(i).Range.Text
Case "Sept": xlWkSht.Cells(lRow, 3).Value = .ContentControls(i).Range.Text
Case "Dec": xlWkSht.Cells(lRow, 4).Value = .ContentControls(i).Range.Text
End Select
Next
where 1, 2, 3 & 4 represent the destination column #s.

[jmdeland]

That was it!! An endless thank you sir, you are my hero!

For anyone else that may find this useful - all credit to Macropod:


Sub InsertFormfieldResults()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, StrFlNm As String
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
SendKeys "%n *.doc ~"
If .Show = True Then
StrFlNm = .SelectedItems(1)
Else
Exit Sub
End If
End With
Dim xlWkSht As Worksheet
Set xlWkSht = ActiveSheet
lRow = xlWkSht.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(Filename:=StrFlNm, AddToRecentFiles:=False)
With wdDoc
For i = 1 To .ContentControls.Count
Select Case .ContentControls(i).Tag
Case "Mar": xlWkSht.Cells(lRow, 1).Value = .ContentControls(i).Range.Text
Case "Jun": xlWkSht.Cells(lRow, 2).Value = .ContentControls(i).Range.Text
Case "Sept": xlWkSht.Cells(lRow, 3).Value = .ContentControls(i).Range.Text
Case "Dec": xlWkSht.Cells(lRow, 4).Value = .ContentControls(i).Range.Text
End Select
Next
.Close SaveChanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub