PDA

View Full Version : what is the best way to populate word specific locations from Excel Data



flora
07-03-2019, 04:08 AM
Hello,

I have a long word documents that has charts, tables, and paragraphs.

the charts, tables and some numbers in the paragraph comes from an excel file. but the process is very manual. like copying from excel manually and pasting them in word.

is there a way this could be automated?

thanks.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=320960#p320960 (https://eileenslounge.com/viewtopic.php?p=320960#p320960)
https://eileenslounge.com/viewtopic.php?p=320957#p3209573 (https://eileenslounge.com/viewtopic.php?p=320957#p3209573)
https://eileenslounge.com/viewtopic.php?p=318868#p318868 (https://eileenslounge.com/viewtopic.php?p=318868#p318868)
https://eileenslounge.com/viewtopic.php?p=318311#p318311 (https://eileenslounge.com/viewtopic.php?p=318311#p318311)
https://eileenslounge.com/viewtopic.php?p=318302#p318302 (https://eileenslounge.com/viewtopic.php?p=318302#p318302)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317857#p317857 (https://eileenslounge.com/viewtopic.php?p=317857#p317857)
https://eileenslounge.com/viewtopic.php?p=317541#p317541 (https://eileenslounge.com/viewtopic.php?p=317541#p317541)
https://eileenslounge.com/viewtopic.php?p=317520#p317520 (https://eileenslounge.com/viewtopic.php?p=317520#p317520)
https://eileenslounge.com/viewtopic.php?p=317510#p317510 (https://eileenslounge.com/viewtopic.php?p=317510#p317510)
https://eileenslounge.com/viewtopic.php?p=317547#p317547 (https://eileenslounge.com/viewtopic.php?p=317547#p317547)
https://eileenslounge.com/viewtopic.php?p=317573#p317573 (https://eileenslounge.com/viewtopic.php?p=317573#p317573)
https://eileenslounge.com/viewtopic.php?p=317574#p317574 (https://eileenslounge.com/viewtopic.php?p=317574#p317574)
https://eileenslounge.com/viewtopic.php?p=317582#p317582 (https://eileenslounge.com/viewtopic.php?p=317582#p317582)
https://eileenslounge.com/viewtopic.php?p=317583#p317583 (https://eileenslounge.com/viewtopic.php?p=317583#p317583)
https://eileenslounge.com/viewtopic.php?p=317605#p317605 (https://eileenslounge.com/viewtopic.php?p=317605#p317605)
https://eileenslounge.com/viewtopic.php?p=316935#p316935 (https://eileenslounge.com/viewtopic.php?p=316935#p316935)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317014#p317014 (https://eileenslounge.com/viewtopic.php?p=317014#p317014)
https://eileenslounge.com/viewtopic.php?p=316940#p316940 (https://eileenslounge.com/viewtopic.php?p=316940#p316940)
https://eileenslounge.com/viewtopic.php?p=316927#p316927 (https://eileenslounge.com/viewtopic.php?p=316927#p316927)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein
07-04-2019, 04:27 PM
Hello flora,
Welcome to excelfox

I cannot answer fully your question as I have limited experience with Word VBA, and no experience with charts.

I can give you one short example based on a coding which I use daily. It might give you at least one idea. But it is a bit of a long way around, - Possibly there is a better way to do it. (My way uses a lot of HTML coding , because in daily use I send the final complete text and tables, as seen in the final Word file, as an Email, automatically.)
The coding is a bit rough and patchy, as I have done a quick modified version of an existing coding of mine. I have not explained it all too well. It is intended to give you just a taste of what I can do. If you want to take this further , and provided you are not in too much of a rush, then I can expand on a few things or explain more, etc. over the next few days..

I am using Word 2007 and Excel 2007 for this example.

As a short example, lets say, my Word file looks like this simple text:

Here is my Word File, "WordFile.htm",
WordFile BEFORE.JPG: : https://imgur.com/eyXdoTq
2334

I would like to put here Table1.
Here is some other text.

(The Excel file is uploaded for you below. I had to use an external File sharing site, ( app.box.com ) for the .htm File,because that file format will not upload to excelfox . Alternatively download the .docx file below, and resave as .htm from within Word : docx to htm SaveAs.jpg : https://imgur.com/LdHyWst ).

(It simplifies the coding to have this saved with that untypical extension, .htm . But additional coding could allow that to be saved as a normal .doc or .docx Word File)

In an Excel file I have this:
ExcelFile.jpg: : https://imgur.com/G20XRwo
2335
_____ Workbook: Excel File.xls ( Using Excel 2007 32 bit )
Row\Col
A
B

1Table1 Header


2ab


3cd
Worksheet: Tabelle1
The short macro routine example that I give you is also in the above Excel File, "ExcelFile.xls". (The file is uploaded for you below). I also have the total coding that you need here:
http://www.excelfox.com/forum/showthread.php/2348-Just-Testing-Passing-info-between-Word-and-Excel?p=11386&viewfull=1#post11386


After running the routine , Sub MakeTagList() , you will get a new Word document, "WordFile2.htm" which looks like this:
WordFile2 AFTER.JPG : https://imgur.com/1QNLExu
2336


This is what you need to do for the demo of what is going on:
Download both the files. ( The Excel File and the htm Word File ). Save them anywhere, but make sure they are both saved in the same place. Run the routine , Sub MakeTagList()
The new word doco, "WordFile2.htm" , which is produced, will show shortly for about 5 seconds.

Let me know if you want to take this further.

Alan


Ref:
http://www.eileenslounge.com/viewtopic.php?f=27&t=29556#p228710




P.S.
I think you may get to a larger audience of more experienced people in the area of automating Word and Excel somewhere else, such as here:
https://www.excelforum.com/word-programming-vba-macros/
http://www.eileenslounge.com/viewforum.php?f=30
(If you post your question elsewhere , then you might want to mention that you already posted the question at excelfox: This is because forums usually have some Rules about "cross posting", which basically means that you must say where else you have posted the same question. http://www.excelfox.com/forum/showthread.php/1172-Message-To-Cross-Posters )

Alan


"WordFile.htm" : https://app.box.com/s/xl1l7noo2nf7znnzyz6evqaeiuu37gcz

flora
07-12-2019, 03:53 AM
Thank you DocAElstein for your willingness to help. appreciated.

I downloaded the two files. when i run the macro. it only created two htm files and nothing into the word.

DocAElstein
07-12-2019, 12:18 PM
Hello flora,
I think possibly you may not have fully understood all that I tried to explain. The way I am showing is a bit complicated.

So do this:

_1 When you have time, read again carefully all that I have written in post #2 ( http://www.excelfox.com/forum/showthread.php/2347-what-is-the-best-way-to-populate-word-specific-locations-from-Excel-Data?p=11387&viewfull=1#post11387 ) . One thing that is important to understand is that you need just to have 2 files initially. One is the excel file, "ExcelFile.xls" , and the other is the htm file , "WordFile.htm"

So now:
_2 Delete all the files that you have downloaded so far.

_3 Start again. Download the two files
"ExcelFile.xls" ( It is attached to the posts: ExcelFile attatched to post.JPG : https://imgur.com/lRfKrkD , https://imgur.com/lCcz329 )
"WordFile.htm" ( It is not attached. You must
Either:
Download from here : https://app.box.com/s/xl1l7noo2nf7znnzyz6evqaeiuu37gcz
( Or alternatively;
Download "WordFile.docx" and resave it as "WordFile.htm". ( docx to htm SaveAs.jpg : https://imgur.com/LdHyWst )
Then delete "WordFile.docx" ) )

Both files must be saved in the same place.
You must have initially just two files saved : "ExcelFile.xls" and "WordFile.htm"

_4 Now run Sub MakeTagList()


Remember that I am showing you just one idea. There are probably many other better ways to do what you want to do. But I only know about this one way.

Alan

( P.S: you can open the htm files later using word: Open htm file in Microsoft Word.JPG : https://imgur.com/JorCuQL )

flora
07-14-2019, 07:00 AM
Thank you DocAElstein

DocAElstein
07-15-2019, 04:30 PM
Thank you DocAElstein
You are welcome. :)
Good luck with your project.

Alan