VBA Macro: Excel data that will imports data into a word doc
Hello everyone,
I have a task that will be extensive in the making. Here are the details:
I have a word document that has a made up paragraph. I am wanting to be able to run a macro in word or excel, and have it copy and paste the words specified in excel and import into the paragraph in word.
Can someone please provide me with a code that I can modify to do this?
Example: Cat that cost $40.00 is what should be pasted below. I have attached the word doc and excel spreadsheet.
Dear Santa,
I am wishing you deliver me one of the following animals.
1. Cat that cost _______
2. Dog that cost _______
3. Rabbit that cost ________
4. Snake that cost ________
I have been really good this year!!
Data-extract.xls Paragraph.docx
I have a task that will be extensive in the making. Here are the details:
I have a word document that has a made up paragraph. I am wanting to be able to run a macro in word or excel, and have it copy and paste the words specified in excel and import into the paragraph in word.
Can someone please provide me with a code that I can modify to do this?
Example: Cat that cost $40.00 is what should be pasted below. I have attached the word doc and excel spreadsheet.
Dear Santa,
I am wishing you deliver me one of the following animals.
1. Cat that cost _______
2. Dog that cost _______
3. Rabbit that cost ________
4. Snake that cost ________
I have been really good this year!!
Data-extract.xls Paragraph.docx
This is very close. Take a look. Download the two files and put in the same directory. Then Run the copyDataToWord() macro on the Data-extract.xls file, it will open the DOC file for you to see after it finishes.
It uses bookmarks I put in the paragraph.
If you like, and you respond to the above question in my prior post, I'll finalize.
Dave
Data-extract-r1.xls
SantaParagraph.docx
It uses bookmarks I put in the paragraph.
If you like, and you respond to the above question in my prior post, I'll finalize.
Dave
Data-extract-r1.xls
SantaParagraph.docx
ASKER
Hey Dave!
Good to hear from you... So here is the scope for why I am doing this automation. I have a spreadsheet that has KIR values for every week. What is happening is that I am editing a word document that is the executive report that is summaries the weekly KIR's. So example, it would say this week we had 100 alarms which was a decrease from the previous week (110). So basically, what I want to do is write a script that will take the values that are in the spreadsheet and replace the old values like 110 to 100 and increase to decrease. this document is about 4 pages long and takes a good 3 hrs to fully change all the data.. It also shows graphs so that needs to be imported as well. I have a good idea on how to get the excel spreadsheet ready for import. Such as using offsets for the graphs, and making decrease and increase populate. The problem is how to tell it to import at certain location on the document. I am thinking that in the spreadsheet it will have to look at the previous week and know to do a replace with the newest data into the document. However, that present some challenges because what if 110 alarms actually are 110 tickets. All the dat would be changed. Some how I will need to create named ranges in word and named ranges in excel and use that to be the replacing factor. Ideas?
Good to hear from you... So here is the scope for why I am doing this automation. I have a spreadsheet that has KIR values for every week. What is happening is that I am editing a word document that is the executive report that is summaries the weekly KIR's. So example, it would say this week we had 100 alarms which was a decrease from the previous week (110). So basically, what I want to do is write a script that will take the values that are in the spreadsheet and replace the old values like 110 to 100 and increase to decrease. this document is about 4 pages long and takes a good 3 hrs to fully change all the data.. It also shows graphs so that needs to be imported as well. I have a good idea on how to get the excel spreadsheet ready for import. Such as using offsets for the graphs, and making decrease and increase populate. The problem is how to tell it to import at certain location on the document. I am thinking that in the spreadsheet it will have to look at the previous week and know to do a replace with the newest data into the document. However, that present some challenges because what if 110 alarms actually are 110 tickets. All the dat would be changed. Some how I will need to create named ranges in word and named ranges in excel and use that to be the replacing factor. Ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! Just what I need to get the ball rolling. Thanks you so much!
This tip: http://vbadud.blogspot.com/2009/08/update-word-document-with-excel.html should get you there.
Give me a sec, and I'll apply.
PS =- are you generating a lot of letters (re: need to work from a Word template, creating several letters?) having multiple rows in Excel to populate multiple letters?
Let me know,
Dave