[VBA] Exporting Word 2003 Form Data to Excel

[Canadian_Newb]

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

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…. (

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!

 

[Trevor G]

I have downloaded your samples. I have adjusted the code and it works. What you will need to add is all the variable options for your Tickbox names from your Word document to fit into the code. I have done the first one which is for the Occupation if Retired And Full Time because of your example Word Documents. I have also taken off Freeze Pane

Here is a sample of running the code and how it looks in the workbook.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 88px"><COL style="WIDTH: 88px"><COL style="WIDTH: 92px"><COL style="WIDTH: 77px"><COL style="WIDTH: 86px"><COL style="WIDTH: 125px"><COL style="WIDTH: 127px"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 35px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">First Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Last Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Email</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">OCT</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Employment
Type</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">School Board</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">School Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Comments</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>June</TD><TD>David</TD><TD>Gaudet</TD><TD>da@da.com</TD><TD> </TD><TD>Retired</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 68px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>June</TD><TD>Joe</TD><TD>Tester</TD><TD>da@da.com</TD><TD> </TD><TD>Full Time</TD><TD>School School</TD><TD>chhol Board Board</TD><TD>I would like to express my interest in the rofjhsdafn sdfasildfasdjlkfa dfo9asdjadfaopifu </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

And here is the code adjusted, please adjust file path and also you will have to add the if statements where I have left a comment.

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:\A Teacher"
'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("b3").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = curDoc.FormFields("DATE").Result
ActiveCell.Offset(0, 1).Value = curDoc.FormFields("FNAME").Result
ActiveCell.Offset(0, 2).Value = curDoc.FormFields("LNAME").Result
ActiveCell.Offset(0, 3).Value = curDoc.FormFields("EMAIL").Result
ActiveCell.Offset(0, 6).Value = curDoc.FormFields("OCT").Result

'You need to know the formfield names in the word document
'In this case I am using single if statements.
'This will work for you, but may not be the smartest way

If curDoc.FormFields("RET").Result = True Then
ActiveCell.Offset(0, 5).Value = "Retired"
End If
If curDoc.FormFields("PERMFT").Result = True Then
ActiveCell.Offset(0, 5).Value = "Full Time"
End If


ActiveCell.Offset(0, 6).Value = curDoc.FormFields("SBOARD").Result
ActiveCell.Offset(0, 7).Value = curDoc.FormFields("SNAME").Result
ActiveCell.Offset(0, 8).Value = curDoc.FormFields("COMMENTS").Result

curDoc.Close
'Get the next document
wdDoc = Dir()
Loop
wdApp.Quit
End Sub

 

[Canadian_Newb]

Oh gosh that was amazing! I’ve just now completed it (a lot of stuff, lol, it does work for me but you’re right – there was probably a more efficient way to do this. But in light of my novice VBA experience, this works like a charm!

Thank-you so very much, Trevor! You’ve been an amazing help with this. Here’s your e-cookie! *e-cookie* )

BUT (lmao) I have one very last question and then we can mark this as mission accomplished!

For column “I” School Board, there are two possible places that this info can come from (they are text fields this time). How would I structure the code to reflect this? Some combination of the IF and the other code?

This is what I have now but it’s not working (since one text field is empty and the other is filled it is just writing a blank field to the cell…?) Something I’ve done here is wrong but I am a little lost as to how to remedy this one.

If curDoc.FormFields("RET").Result = True Then
ActiveCell.Offset(0, 6).Value = "Retired"
End If
If curDoc.FormFields("PERMFT").Result = True Then
ActiveCell.Offset(0, 6).Value = "Full Time"
End If
If curDoc.FormFields("LTOS").Result = True Then
ActiveCell.Offset(0, 6).Value = "Long Term Occ/Supply"
End If
If curDoc.FormFields("OTHER").Result = True Then
ActiveCell.Offset(0, 6).Value = "Other"
End If

ActiveCell.Offset(0, 7).Value = curDoc.FormFields("SBOARD").Result
ActiveCell.Offset(0, 7).Value = curDoc.FormFields("LTSBOARD").Result
ActiveCell.Offset(0, 8).Value = curDoc.FormFields("SNAME").Result
ActiveCell.Offset(0, 9).Value = curDoc.FormFields("COMMENTS").Result

THANK-YOU!!!!!!!!

 

[Trevor G]

Thanks for the ecookie. Nice idea.

I would look to run with an IF statement like this.

<font face=Courier New><SPAN style="color:#00007F">If</SPAN> curDoc.FormFields("SBOARD").Result = "" <SPAN style="color:#00007F">Then</SPAN><br>ActiveCell.Offset(0, 7).Value = curDoc.FormFields("LTSBOARD").Result<br><SPAN style="color:#00007F">Else</SPAN><br>ActiveCell.Offset(0, 7).Value = curDoc.FormFields("SBOARD").Result<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br></FONT>

Replace where you had your lines in Bold with this IF statement.