Hi, I can't seem to figure this one out. Thanks for any help you can provide.

Here are the issues:
I am working with Office 2010. I have carefully formatted a worksheet so that it matches a specific output style. The worksheet will have a maximum of three pages with data populated by VBA. The information and format need to be transferred to Word as is. The end-users will need to be able to edit the data.

To complicate it even more, my company has a specific .dotm they want us to use in Word - I believe it is set up as a COM-Add-in so I do not have access to it. The only way I have been able to launch the company-style is by using SendKeys. I know - I've heard the bad press online but I can't figure out any other way to launch the style.

A manual copy-paste while keeping source formatting still messes up the table formatting - it doesn't leave columns lined up properly in the Word table.

A manual copy-paste as an embedded object causes a corruption error when trying to open the object in Word.


I have some code set up that does this:
1) Brings Word into focus.
2) Uses SendKeys to open the company style.
3) Saves the specific worksheet to the end-users temp directory as a VBA-and-clutter-free workbook.
4) Copies a range and pastes to an un-linked embedded object into Word.


What doesn't work with this code:
1) The entire worksheet is embedded instead of just the range, I need to have each page/range pasted seperately into Word.
2) I can't get the code to bring Excel back into focus so that the end of macro message box pops up. I read that you need a specific workbook name to do this but my end-users will likely have different file names - my long-term goal is to turn this into an Add-in.
3) The embedded object doesn't seem to act like a normal object. You can context-menu edit it but if you select a new range and close back out of the Excel object, it won't change to your newly selected range. I don't know if that makes a difference - if I can get the ranges to copy-paste seperately, this shouldn't matter, I woudln't think.


Here's the code:
Please Login or Register  to view this content.