Populate Text Form Fields from excel worksheet

[framcc06]

Hello,

I have a Word template locked for filling in forms and a spreadsheet with data that corresponds to each field. What I want to happen is for when the user enters a word/string in the first text form field that matches what is in spreadsheet (eg column B) it will fill in the remaining text fields from that row (probably on the Run macro on exit command) else if the word/string cannot be found it will return an error message.

I can upload example template & spreadsheet if needed.

Thanks

fra

[macropod]

Is there a reason for not doing this with a mailmerge, where you can just select the desired record and have Word extract all the required data from Excel?

[gmayor]

See the thread - http://www.vbaexpress.com/forum/showthread.php?60751-Read-Excel-Cell-from-Word-VBA which shows how to write the worksheet to an array, which you can interrogate to find the record you want and consequently the rest of the information in that row.

[framcc06]

Hi Paul, thought about that but decided it was'nt practical.

Graham I will give that a try and reply later today with result.

Thanks
fra

[framcc06]

Hi Graham,

The first text field in Word would have to act like a search function which finds an exact match in the spreadsheet then fill in the remaining fields in Word based off that row.

fra

[gmayor]

That should be fairly straightforward, but I would need to see the document.
Can you post the document and the worksheet as you indicated you would in your first message.

[framcc06]

Thanks Graham, please see attached.

fra

[framcc06]

Sorry just realised one of the columns was missing in worksheet

[gmayor]

Hmmm. While what you propose is simple enough, it looks as though you are sending this form to the client for completion. The client is almost certainly not going to have access to your database and so will not be able to fill the form using data from it. You will need to complete that part of the data before sending out the forms.

What you need here is a combination of mail merge and form fields and Word on its own does not do that. My suggestion would be http://www.gmayor.com/ManyToOne.htm which will combine protected forms with mail merge fields. You would replace the formfields with merge fields in the first part of the document and complete the merge either to separate documents or to e-mail.

See also http://www.gmayor.com/ExtractDataFromForms.htm (http://www.gmayor.com/ExtractDataFromForms.htm) which should help when the forms are returned.

[framcc06]

Many thanks Graham thats exactly what I'm after - I will mark this as solved. Just out of interest what would the code be or could you provide a link if they were internal clients who would have access to the network drive the worksheet is stored on?

fra