exporting data from excel into word

Hello everybody. I have a very indepth spreadsheet at work. We also have a form that was typed up in word that uses some of the information from the spreadsheet. I was wondering if there is a way to have the information from the spreadsheet autofill the form that is in word?

Hello pboost1,

Yes you can transfer information between the two programs. You would need to post both the worksheet and the Word document before anyone can answer any specific questions for you.

Sincerely,
Leith Ross

Since the form and spreadsheet is for work, I have to make some changes (security reasons) cause it was dealing with customer information. What I would like to do is when I enter the form number in the word document, to have the name of the people, and the mailing address autofill from the excel spreadsheet.

For example: All the information that is equated to data form 1 to fill in on the word document when 1 is entered into the form number section of the Data form.

Any help would be greatly appreciative. And Thanks in advance.

Hello pboost1,

I have a few questions for you about the workbook and document. First, I noticed you are using Drop Downs on the the document. Do you intend to have the user select the form number from the Form drop down? Will the other drop downs be used as drop downs. The data on the worksheet doesn't appear to match your document. Can you list which cells go where in the document?

Sincerely,
Leith Ross

Sorry for the confusion. I would like something similiar to =vlookup in the word document.

For example, on the word document, when I enter a number 1 in the form number box, I would like Mickey mouse to fill in Name of customer (a), Minnie Mouse in Name of customer (b), the renewel date of 6-3-08 to fill in the date of form, and mickey's address to fill in the primary address.


to translate it to cells, in the word document, when I enter 1 in the form number box, i would like 'Complete Info'B2 to fill in name of customer(a), 'Other People'B2 to fill in box (b), 'People'H3 to fill in date of form box, and a concatenate of 'People'D2 & 'Complete Info'c2 to fill in the primary mailing address.


I would also like the same thing to happen when I enter 2, 3, or any other number from the data form column.


I would like this to fill in the drop down form box from excel to word. i hope this makes it clearer.

Hello pboost1,

The attached zip file contains both the Excel file and the Word Document. The Word document has macro code added to copy the Excel data into the Drop Down fields. You have to click on each field in order to fill it. This is just the nature of the Word FormField DropDown. You may want to use another control. The Word document checks that the Excel workbook is running. If not the macro will inform of this. If you change tha name of the workbook, you must also change it in the Word document macro code. Test this out and let me know what you think about it.

Sincerely,
Leith Ross

Hello there,

I tried the samples you have given and am thankful for all that you have done. However, when I tried to run the macro, I get a "Run Time error 91 Object variable or with block variable not set".

When i clicked on debug, the line that is highlighted is "set wks = xlwkb, worksheets ("Complete info").

Unfortunately, I don't know really know any programming so i can't even try to fix this on my own. Again thanks for all that you have done so far.

Hello pboost1,

Looks like a typo. the line should read...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hello there, i did type it wrong, but when I compared your line with the line that is in yellow, they appear to be the same. and I am still getting that error.

Am I doing something wrong?

Hello pboost1,

Try changing the declarations at the very top of the macro module. The code runs fine on my machine. Any variable declared in the General Declarations section of VBA module should be available to the entire VBA project. I used a Dim statement. So, to force it to be available to the project, change the Dim statements to public.

Code Currently

Please Login or Register  to view this content.

Code Change

Please Login or Register  to view this content.

Sincerely,
Leith Ross

I think i found out what I was doing wrong. i was opening the word document first, then excel. I tied opening excel and then word and that seems to work.

I now have to figure out how to copy this for what I have at work. I have about 800 forms that I will need to have this work for.

Again my programming skills are not the greatest. I will have to look at what you have and hopefully be able to copy it over.

If I may ask one question, I see you are using the drop down box, which makes things easier than retyping the data, is there a way to have it auto fill without using the drop down box? If not, I thank you enough for all that you have done.

Hello pboost1,

I didn't change the drop down boxes as they were part of the original form you sent me. It seemed more logical to have a single drop down for the Form Numbers and auto-fill the remaining sections. The other fields can be changed to auto-filled, but that means I will need to alter the macro as well. If that is what you want to do, I will need some time to make those changes. Let me know.

Sincerely,
Leith Ross

Yes, please. I would like to be able to enter the one data form number and have everything else autofill. If that could be done, that would be great. Thanks.

Hello pboost1,

The attached Word document has been amended. It will now auto-fill the text on the template when you select a form number.

Sincerely,
Leith Ross

The sample works great. I thank you very much. I just need to adapt what you have given to what I have at work. Thank you again.

Would you be willing to explain how you did this? If you don't want to or feel comfortable, I definitely understand. You have helped out a lot with all that you have done. Thank you again.

Have you tried to do a merge file from word using the excel file fields?

I am not sure how to do a merge file or what it is. Could you please elaborate as to what this is and how to do it?