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:
Option Explicit
 Sub ExcelToWord()
'Sends output to Word in a pitch document

Dim wrdApp As Word.Application
Dim wb As Workbook

    Set wrdApp = Word.Application
    wrdApp.Visible = True
    
'----------------------------------------
'Opens a new pitch page in Word
'----------------------------------------
    'This brings Word into focus so that Sendkeys can work
    Application.ActivateMicrosoftApp xlMicrosoftWord
    
    '%=ALT; then the shortcut keys Q N S to insert new pitch page, so = ALT-Q-N-S
    SendKeys ("%qns"), True 'sendkeys turns numlock off
    SendKeys "{NUMLOCK}", True 'turns numlock back on
        
'----------------------------------------
'Set up workbook in temp folder to copy from
'----------------------------------------
'Delete the workbook from the users TEMP folder if it's already there
    If Dir(Environ("temp") & "\output_safe to delete.xls") <> "" _
        Then Kill Environ("temp") & "\output_safe to delete.xls"
    
'The user's TEMP folder is Environ("TEMP"), so this
'will copy the file to the TEMP folder
    Worksheets("Output").Copy
    Set wb = ActiveWorkbook
    wb.SaveAs Environ("temp") & "\output_safe to delete.xls"
    'wb.Close
        
'----------------------------------------
'Format and copy data from temp workbook
'----------------------------------------
'Make data in range Values only with no formulas
    Range("A1:H44").Copy 'H135
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
'Clear extra data
    Rows("140:1048576").Delete
    Columns("I:XFD").Delete
    
'Copy range to send to Word
    Range("A1:H44").Copy
    Range("A1").Select
                  
'----------------------------------------
'Paste object into word
'----------------------------------------
'Paste into Word
    wrdApp.Application.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
        :=wdInLine, DisplayAsIcon:=False
    
    Set wrdApp = Nothing

'----------------------------------------
'Close temp workbook
'----------------------------------------
    ActiveWorkbook.Saved = True
    Application.CutCopyMode = False
    wb.Close
    Application.CutCopyMode = True

'----------------------------------------
'Go back to Excel and complete message box
'----------------------------------------
'This brings Excel back into focus
    'AppActivate "wgltest", True

'Alert to end of macro
    MsgBox "The Working Group List is ready in MS Word."
End Sub