How Do I Late Bind This...

[Trikageon]

Hey all! I was wondering if anyone knew copy range G15:H600 from excel to word, paste it, and then copy it back from word to excel in the exact same location from whence it came - all using late binding. I've been trying everything I can think of, but I have very little experience in this regard. If you could just write back code I could try, I would be ecstatic if something actually worked! Thanks in advance!

 

[John_w]

Try this - change file paths and names as required. But why do you need it to use late binding? Do you want to run it from a VBScript or JScript file?

Option Explicit

Sub Excel_Word_Copy()

    Dim exApp As Object, exWb As Object
    Dim wdApp As Object, wdDoc As Object, wdTable As Object
    
    'Open an existing Excel workbook
    
    Set exApp = CreateObject("Excel.Application")
    With exApp
        .Visible = True
        Set exWb = .Workbooks.Open("C:\Temp\Excel_Workbook2.xls", , False)
    End With

    'Open an existing Word document
    
    Set wdApp = CreateObject("Word.Application")
    With wdApp
        .Visible = True
        Set wdDoc = .Documents.Open("C:\Temp\Word_Document.doc")
    End With

    'Copy the Excel range to the clipboard
        
    exWb.Worksheets("Sheet1").Range("G15:H600").Copy

    'Paste it at the start of the Word document - the data is pasted as a table
    
    wdDoc.Content.Paste

    exApp.CutCopyMode = False

    'Copy the first table in the document - the table that has just been pasted from Excel
    
    Set wdTable = wdDoc.Tables(1)
    wdTable.Range.Copy
    
    'Paste it back to the Excel workbook starting at G15
    
    With exWb.Worksheets("Sheet1")
        .Range("G15:H600").Cells.ClearContents  'Not required, but proves Word data has been pasted
        .Range("G15").Select
        .Paste
    End With
    
    'Close document

    wdDoc.Close True
    wdApp.Quit

    'Close workbook

    exWb.Close True
    exApp.Quit

End Sub