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

sample.doc

[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.
________________________________________

sample.doc
.

[Humphrey]

Here is the spreadsheet ..

sample.xls

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-prog...65#post2909165

http://forums.techguy.org/business-a...ml#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:
[VBA]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[/VBA]

[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-a...ml#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-a...ml#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:
[VBA]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[/VBA]