Thread: Transfer data from Excel to word using VBA script

Hi Welcome to the forum,

Try this code

Code:

Sub LinkWorkBookToMsWord()

'This code will Paste an Excel range  to Ms_Word and name it, and it will be linked to the excel workbook

    Dim xlTable As Object
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("A1", Range("B65536").End(xlUp))
    Set xlTable = CreateObject("Word.Application")

    xlTable.Visible = True
    r.Copy
    xlTable.documents.Add
    xlTable.Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
                                   wdInLine, DisplayAsIcon:=False
    xlTable.activedocument.SaveAs ThisWorkbook.Path & "/" & "LinkedToWord.doc"
    xlTable.documents.Close
    xlTable.Quit
    Application.CutCopyMode = False
End Sub

Dave,

I think your code might end up in an error as

Code:

Set r = Worksheets("Sheet1").Range("A1", Range("B65536").End(xlUp))

will only work when Sheet1 is ActiveSheet. I think it is better to do it as such:

Code:

Sub SetRng()
Dim r As Range

'Set r = Worksheets("Sheet1").Range("A1", Range("B65536").End(xlUp))

With Worksheets("Sheet1")
  Set r = Range(.Range("A1"), .Range("B65536").End(xlUp))
End With

End Sub