Linking MS Word form with Excel spreadsheet

[jab40]

Hi - I wonder if anyone could help me?

Basically, I have an application form, which I email out for people to complete and return to me. The form is one I inherited - ie I did not build it and do not know how it works: all I know is that it is locked for editing except for form fields for people to fill in the relevant information.

When I get these application forms back, I then need to enter this information into an Excel spreadsheet, so that all of the information for all applicants is stored together.

This would be a fairly simple spreadsheet, with column headers such as Reference (the unique identifier), name, location etc etc - basically, all the information asked for in the application form.

Is there a way that I can build a spreadsheet to tell it to 'look' in the right place in each application form document to populate itself with this information - so that I don't have to manually copy and paste? Ie, I want to suck the information automatically from the application form into Excel.

I know that I could redo the form in Excel or something to negate this problem, but unfortunately this is not currently an immediate option as it is already in use for this particular round - whilst I can do this in the future, for now I must stick with the original form.

Any help that anyone might be able to offer will be much appreciated.

J

 

[Trevor G]

You need to set the reference as I stated in my thread.

You will need to set the reference to Word in the VBA screen so use Alt + F11 and then select Tools > References > Microsoft Word XX Object Library

 

[jab40]

Oh yes - you are right, sorry. I thought I had done that but seemingly not!

It seems to be working - I can see an excel sheet with the information in. However, the macro(?) still seems to be running, and I keep getting a message coming up reading 'Microsoft Excel is waiting for another application to complete an OLE action', to which I can only click OK. It then busys itself for another short while before the same message appears again.

 

[Burrgogi]

Thanks ever so much. Before I proceed, I should warn that I have little coding experience, and no experience with VBA.

I have done as you said, changing the file name and location, and bookmark names etc. I have then put the cursor to the top of that code and pressed the 'play' button.

I immediately get a compile error, saying that the 'user-defined type not defined', highlighting the 'wrd As Word.Application' part of the text in the second line.

How should I proceed?

The first thing I would check is to make sure that you have set the proper reference in the library as Trevor outlined in his directions to you. Look at the screenshot.

Edit:
I see you're using Office 2010 in which case you will probably see "Microsoft Word 13" in the references library.

Never mind - I just saw Trevor's response to you.

 

[Burrgogi]

Oh yes - you are right, sorry. I thought I had done that but seemingly not!

It seems to be working - I can see an excel sheet with the information in. However, the macro(?) still seems to be running, and I keep getting a message coming up reading 'Microsoft Excel is waiting for another application to complete an OLE action', to which I can only click OK. It then busys itself for another short while before the same message appears again.

Open up the VBA editor (Alt + F11).

In the the toolbar, press stop. Or under the Run menu > "Reset".

 

[Trevor G]

The first thing I would check is to make sure that you have set the proper reference in the library as Trevor outlined in his directions to you. Look at the screenshot.

Edit:
I see you're using Office 2010 in which case you will probably see "Microsoft Word 13" in the references library.

Never mind - I just saw Trevor's response to you.

Microsoft Skipt 13 as it is deemed to be unlucky, so 2010 is 14 in the library references.

 

[jab40]

Open up the VBA editor (Alt + F11).

In the the toolbar, press stop. Or under the Run menu > "Reset".

this doesn't work, unfortunately ...

 

[Trevor G]

Oh yes - you are right, sorry. I thought I had done that but seemingly not!

It seems to be working - I can see an excel sheet with the information in. However, the macro(?) still seems to be running, and I keep getting a message coming up reading 'Microsoft Excel is waiting for another application to complete an OLE action', to which I can only click OK. It then busys itself for another short while before the same message appears again.

Try adding this near the bottom of the code. (I haven't written any VBA in 2010 so not so sure why it would do this).

wrd.Quit

So your code would now look like this

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
wrd.Quit
End Sub

 

[jab40]

Try adding this near the bottom of the code. (I haven't written any VBA in 2010 so not so sure why it would do this).



So your code would now look like this

that is bloomin' beautiful - you have no idea how happy that makes me! Thanks!

(it works, by the way).

So my next question is, this works well for one application form, putting all of the data into one row. But how do I get it to then look at the next application (and the next ...) etc, and put the information into row underneath the last filled row? Is this possible?

 

[Trevor G]

I have just written this for you and tested it, you will have to change some things, file path, if doc or docx and cell ids

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 = "M:\Access Files\docs"
'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(1, 0).Select

ActiveCell.Offset(0, 0).Value = curDoc.FormFields("MyName").Result
ActiveCell.Offset(0, 1).Value = curDoc.FormFields("myTitle").Result
ActiveCell.Offset(0, 2).Value = curDoc.FormFields("mySurname").Result
ActiveCell.Offset(0, 3).Value = curDoc.FormFields("myJobTitle").Result
curDoc.Close
'Get the next document
wdDoc = Dir()
Loop
wdApp.Quit
End Sub

 

[Burrgogi]

Microsoft Skipt 13 as it is deemed to be unlucky, so 2010 is 14 in the library references.

That's good to know for future reference. Thanks.