[VBA] Exporting Word 2003 Form Data to Excel

Canadian_Newb

New Member
Joined
Jun 30, 2011
Messages
31
I need to accomplish a few things here…and I’m a newbie @ VBA so please bear with my seemingly ignorant questions…but I’m in a bit of a pickle.

I have a form that was created in Word 2003. I need to take this form and once it’s filled out, have the data exported to an Excel workbook.

There are a few stipulations though: there are 3 sections of the form that the user should be able to select 1 choice – Current Teaching Employment, the ‘years’ section of the Teaching Experience part, and the rate your experience level part – 1 selection per line.

What I envision is the user will fill the form out in word, send it back to me, I hit a submit/export button (macro button?) and it sends the data to the Excel spreadsheet…

I don’t know how complicated this may or may not be…but any help or guidance would be of much help! I need to have this done by early next week for work…and I’m afraid that without some help, I will not be able to accomplish this… :o(

I was told that using arrays might be better than what I had attempted before…which was just using straight up VBA that I coy/pasted and modified(if that makes sense….) and it didn’t even work to begin with – syntax errors all over the place…. :o(

Please help me out…

I’ve uploaded the files into a single .zip file so you can see what I’m working with.

http://www.mediafire.com/?p3met3d0imuwat4

Thanks for all/any of the help that you may be able to provide! Anything that you may be able to advise on would be of much value!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Welcome to the forum,

I have recently written something like what you want for someone on the forum,


Your Word document is using lots of Bookmarks, which will be very useful all you will have to workout is where you want the Bookmark results once you identify the names.


I would suggest testing this by using a workbook and add this code behind the workbook..

You need to set up the references to use Word, so Alt + F11 on the Keyboard (Takes you into VBA) then goto Tools Menu > References > Scroll down until you find Microsoft Word XX .object Library and click the box.

Then paste in this code. Adjust what I have highlighted in Red and hopefully this will work for you. Take a look at this code.


Sub wrd12()
Dim wrd As Word.Application
Set wrd = CreateObject("Word.Application")
With wrd
.Documents.Open "M:\Access Files\formFields1.Doc"
.Visible = True

End With
Range("a2").Value = wrd.ActiveDocument.FormFields("MyName").Result
Range("b2").Value = wrd.ActiveDocument.FormFields("myTitle").Result
Range("c2").Value = wrd.ActiveDocument.FormFields("mySurname").Result
Range("d2").Value = wrd.ActiveDocument.FormFields("myJobTitle").Result
End Sub

Also check out this thread as it will help with multiple word documents and adding the form fields to each row

http://www.mrexcel.com/forum/showpost.php?p=2750065&postcount=19
 
Last edited:
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> That code is wonderful! And does the job of transferring exactly as I’d intended! Thank-you so much!

Quick follow up question though – how do I handle the radio boxes? For example, if a user selects “Permanent Contract” – the result in the workbook is ‘0’ where I would prefer to see PERMCON (same name as bookmark). Is that possible with the way I have it set up?

Again, thank-you so very much for your help insofar! :o) :o)
 
Upvote 0
I guess as a follow up to my other question - how should I handle multiple selections on the form for one cell in the workbook?

For example, if I want cell E3 to contain the results (there will only be one selection) from the form section of the same name (possible selections would be PERMCON, NEWGRAD, OCCA, RET) how would I handle that...?

Right now, as I'm testing this - i keep getting a "0" (zero) in those boxes, no matter which one is selected?

Again, thanks a bunch!
 
Upvote 0
Try using an IF statement to get the value you want.

IF FieldName=True Then
Activecell.value="PERMCON"
ElseIF FieldName1=True Then
Activecell.Value="NEWGRAD"
ElseIF FieldName2=True Then
Activecell.Value="OCCA"
Else FieldName3=True Then
Activecell.Value="RET"
End IF
 
Upvote 0
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
thank-you so much for help here so far... :) :) :) quick question – where would I put the IF statement in relation to what you’ve given me so far…? Where do I insert it, because this looks like the statement that I’ll be using for most of this form as it is primarily radio check boxes.<o:p></o:p>
<o:p> </o:p>
Thanks for all your help so far!!! J<o:p></o:p>
 
Upvote 0
Hi,

Okay I’m kind of all over the place here, heh…

This is what I have so far:
Sub Report1()
Dim path As String
Dim wdApp As Word.Application
Dim wdDoc As String
Dim curDoc As Word.Document
Set wdApp = CreateObject("Word.application")
wdApp.Visible = True
path = "C:\Users\xxxxxx\Documents\APPLICATION FORM\FINAL COPIES"
'Get first document in directory
wdDoc = Dir(path & "\*.doc")
'Loop until we don't have anymore documents in the directory
Do While wdDoc <> ""
'Open the document
Set curDoc = wdApp.Documents.Open(path & "\" & wdDoc)
Range("A1").Select
Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Value = curDoc.FormFields("DATE").Result
ActiveCell.Offset(0, 2).Value = curDoc.FormFields("FNAME").Result
ActiveCell.Offset(0, 3).Value = curDoc.FormFields("LNAME").Result
ActiveCell.Offset(0, 4).Value = curDoc.FormFields("EMAIL").Result

curDoc.Close
'Get the next document
wdDoc = Dir()
Loop
wdApp.Quit
End Sub
It’s workin fine for the text fields but I still need to know how to insert the code you recommended for the radio boxes (where to insert in the above code) and another thing that I just noticed with the looping code:

It’s only pulling the info from the last document and inserting it. Or rather, it’s inserting all the info from multiple documents into one row, finishing with the last document. Is there something wrong with the code to begin with?

Thanks!
 
Upvote 0
Give this ago. I haven't checked it out due to work commitments. If it bombs take a note of which line of the code fails and post back with all error details.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Report1()<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wdApp <SPAN style="color:#00007F">As</SPAN> Word.Application<br><SPAN style="color:#00007F">Dim</SPAN> wdDoc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> curDoc <SPAN style="color:#00007F">As</SPAN> Word.Document<br><SPAN style="color:#00007F">Set</SPAN> wdApp = CreateObject("Word.application")<br>wdApp.Visible = <SPAN style="color:#00007F">True</SPAN><br>strpPath = "C:\Users\xxxxxx\Documents\APPLICATION FORM\FINAL COPIES"<br><SPAN style="color:#007F00">'Get first document in directory</SPAN><br>wdDoc = Dir(strPath & "\*.doc")<br><SPAN style="color:#007F00">'Loop until we don't have anymore documents in the directory</SPAN><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> wdDoc <> ""<br><SPAN style="color:#007F00">'Open the document</SPAN><br><SPAN style="color:#00007F">Set</SPAN> curDoc = wdApp.Documents.Open(strPath & "\" & wdDoc)<br>Range("A1").Select<br>Selection.End(xlDown).Select<br>ActiveCell.Offset(1, 0).Select<br>ActiveCell.Offset(0, 1).Value = curDoc.FormFields("DATE").Result<br>ActiveCell.Offset(0, 2).Value = curDoc.FormFields("FNAME").Result<br>ActiveCell.Offset(0, 3).Value = curDoc.FormFields("LNAME").Result<br>ActiveCell.Offset(0, 4).Value = curDoc.FormFields("EMAIL").Result<br> <SPAN style="color:#00007F">With</SPAN> ActiveCell.Offset(0, 5).Value<br> <SPAN style="color:#00007F">If</SPAN> FieldName = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Value = "PERMCON"<br><SPAN style="color:#00007F">ElseIf</SPAN> FieldName1 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Value = "NEWGRAD"<br><SPAN style="color:#00007F">ElseIf</SPAN> FieldName2 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Value = "OCCA"<br><SPAN style="color:#00007F">ElseIf</SPAN> FieldName3 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Value = "RET"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>curDoc.Close<br><SPAN style="color:#007F00">'Get the next document</SPAN><br>wdDoc = Dir()<br><SPAN style="color:#00007F">Loop</SPAN><br>wdApp.Quit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Hi, thanks for the info.

This is what I have:

Sub Report1()
Dim path As String
Dim wdApp As Word.Application
Dim wdDoc As String
Dim curDoc As Word.Document
Set wdApp = CreateObject("Word.application")
wdApp.Visible = True
path = "C:\Users\xxxxxx\Documents\APPLICATION FORM\FINAL COPIES"
'Get first document in directory
wdDoc = Dir(path & "\*.doc")
'Loop until we don't have anymore documents in the directory
Do While wdDoc <> ""
'Open the document
Set curDoc = wdApp.Documents.Open(path & "\" & wdDoc)
Range("A1").Select
Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Value = curDoc.FormFields("DATE").Result
ActiveCell.Offset(0, 2).Value = curDoc.FormFields("FNAME").Result
ActiveCell.Offset(0, 3).Value = curDoc.FormFields("LNAME").Result
ActiveCell.Offset(0, 4).Value = curDoc.FormFields("EMAIL").Result
ActiveCell.Offset(0, 5).Value = curDoc.FormFields("OCT").Result
With ActiveCell.Offset(0, 6).Value
If FieldName = True Then
ActiveCell.Value = "PERMFT"
ElseIf FieldName1 = True Then
ActiveCell.Value = "LTOS"
ElseIf FieldName2 = True Then
ActiveCell.Value = "RET"
ElseIf FieldName3 = True Then
ActiveCell.Value = "OTHER"
End If
End With
ActiveCell.Offset(0, 7).Value = curDoc.FormFields("SBOARD").Result
ActiveCell.Offset(0, 8).Value = curDoc.FormFields("SNAME").Result
ActiveCell.Offset(0, 9).Value = curDoc.FormFields("COMMENTS").Result

curDoc.Close
'Get the next document
wdDoc = Dir()
Loop
wdApp.Quit
End Sub
and when I run it I don't get any errors, but it still cycles through the .docs that I have in the folder, but inputs it all on the one row. As well, the IF true statements don't seem to work the way they should - I do not get any results in that column, even though I have some checked.

I've re-upped the files I'm working with because they've been modified a bit.

http://www.mediafire.com/?xxexol8mexwi1ma

Thank-you so much for all the help so far! ;):):)
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top