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]

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?

Have you tried the code I created to loop through a folder?

 

[enreval]

Hi Trevor,

I'm using the loop quote that you have written and i have encountered a runtime error 1004. Could you help please?

When i click Debug, it points to ActiveCell.Offset(1,0).select

 

[Trevor G]

Hi Trevor,

I'm using the loop quote that you have written and i have encountered a runtime error 1004. Could you help please?

When i click Debug, it points to ActiveCell.Offset(1,0).select

Welcome to the forum,

Have you stepped through the code and also set the references as previously mentioned?

 

[enreval]

Hi Trevor G,

Thanks for replying. I have actually solved the problem. But would like to ask with the code you have provided. How do i change it that i can allow the user to select the directory instead of fixing the directory.

 

[Trevor G]

You would need to use something like this

Application.Dialogs(xlDialogOpen).Show

 

[Glaucon]

Welcome to the forum,

Have you stepped through the code and also set the references as previously mentioned?

Hello,

I am brand new to programming (been working through a textbook as quickly as I can), but need some help. I've done everything suggested on this thread (twice now), and I receive the same runtime error as enreval (runtime error 1004 for the line ActiveCell.Offset (1,0).select; however, I am unable to fix the problem. Also, it fails to generate the results from all of the word files in the folder (it only grabs a few of the files). Any help would be great.

 

[proboyz]

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

Hi Trevor,

chanced upon this thread and I followed all your instructions. However, I am unable to confirm if Im bookmarking my forms the right way. what i did is to highlight the content control box, and insert bookmark, and giving it a name. however when i try to run the macro, it gives me the error: the requested member of the collection does not exist. May i know if im doing anything wrong? Im using excel 2010. Thank you very much!