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]

Do you know if your form is using Bookmarks? Which version of Word/Excel are you using? To find out if Bookmarks are being used, Press F5 on the keyboard then select Bookmark, or use the Insert Menu (If using 2003) or the Insert Tab (If using 2007 or 2010).

 

[jab40]

From following your advice, it looks as though it is using bookmarks, yes. This would probably make sense, as the form originally linked to a database that would suck this information in (the database no longer works).

I am on Word and Excel 2010.

Thanks

 

[Trevor G]

Ok then we have a start point. What you need to establish is where the bookmark names are and what data is being stored,within your excel workbook which workisheet and columns you want them to appear in. If you could add some of the bookmark names to your thread and cell ids (even though there will be continued rows etc), I can create a basis for you to work on.

Would be useful to know folder name where the Word document forms are held on completion and returned to you, I will assume once the Word documents have been checked in the Folder then you might want to archive them out of the folder so they don't get used again.

 

[jab40]

Sorry - I thought I did reply to this but it obviously didn't get sent through.

Bookmarks are things like:

'AppAmount' - the amount applied for, which needs to goin in column AQ (first record, row 2)'Appemail' - the applicant's email, which needs to goin in column I (first record, row 2).

Is this what you meant?

As for where the forms are saved, they can be saved wherever is best.

Thanks for your help.

 

[jab40]

Hi - can anyone guide me on the next steps for this, please?

Sorry - I thought I did reply to this but it obviously didn't get sent through.

Bookmarks are things like:

'AppAmount' - the amount applied for, which needs to goin in column AQ (first record, row 2)'Appemail' - the applicant's email, which needs to goin in column I (first record, row 2).

Is this what you meant?

As for where the forms are saved, they can be saved wherever is best.

Thanks for your help.

 

[Trevor G]

Have got it ready for you but called to work in London today. Will reply to your thread over the weekend

 

[jab40]

Have got it ready for you but called to work in London today. Will reply to your thread over the weekend

Trevor, that really is very good of you: very kind. My deepest thanks!

J

 

[Trevor G]

Shown below is a tried and tested method to open a document which has form fields (that have been named) and then copy the contents into the activeworkbook, what I suggest to see that this will work for you is to test it in a new workbook. 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. The insert an new module sheet (Insert Menu) then copy this code. Change the path for it to look at for the document and also the form field names, once tested then you would need to work out all the Form Field Names, the Cells in Excel where you want them to go etc. Post back with questions.

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

 

[jab40]

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?