I trying to automate a process of creating a monthly report - I have an Excel workbook where I update the tables and a few charts are generated from the data, which then I have to transfer to a Word document. The tables and the charts always go to the same places in the Word file, charts are always the same size and the tables are different each month. What I do now is manually delete the old tables/charts from the Word document, then copy the new tables/charts from Excel one by one and paste them into their places in the Word document where I do some formatting so they fit the page.


It is the same process every time and I am trying to figure out a way to do this automatically to save some time. Something like recording a macro that takes stuff from Excel and pastes it into a pre-defined space in Word while deleting the old data and doing some formatting.
Any ideas about how this could be done?

Once upon a time did this from Excel to Powerpoint with, IIRC, OLE (Object linking and Embedding). Essentially, the charts in my Powerpoint were always to the latest state. I'd imagine Excel to Word is similar. A quick search on the intertubes produces this example guide: http://www.homeandlearn.co.uk/excel2...l2007s8p6.html

Thanks. Looks like this might work. I will try it tomorrow.

Assuming you're using the same workbook and charts, why not simply link the two? The easiest way of doing this is to copy the Excel table or chart, then paste it into Word using Paste Special with the 'paste link' option. From then on, any changes to the Excel source will automatically be reflected in the Word document.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, the same was explained in the link that Sekerob gave. My concern is that when I send the Word file to someone else, he will not have the Excel and maybe the tables and the charts will not show up.

If you send the Word file to someone else, you should first unlink the Excel tables and the charts. Otherwise, the recipient may get link errors. You can unlink everything by pressing Ctrl-A, Ctrl-Shift-F9. Better still, save the document as a PDF and send that. That way, you'll know the recipient sees the document with the same layout you have.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I also think saving it as a PDF would be the safest choice, but my colleagues want it in Word. I will send a few test files to see how it goes.

Set documents of this kind shared / forwarded to others, to not update links. Upon opening, there is the question if wanting to update, to which you can then choose to say Yes / No on opening... Have many interlinked workbooks with this setting. It gives a chance to watch the key chart changes happen, but more importantly, allows running snapshot backups of the chart view data that 'was'. Some guidance: https://support.office.com/en-nz/art...b-edaa411a3847
or
https://support.office.com/en-za/art...e-4a84cff3a519