VBA – Transfer data from Excel to Word

With the current article I will show you how to transfer data from Excel to Word. I will use Office 2010 and windows 7.

In the first code snippet, I declare 2 objects and a range. The first Object is the Word Application object (oWord) and the second one is the document itself. As far as the code is ran from an Excel file, no object for the file is needed. The copied used range is simply pasted in the word file. Here is the code:

Option Explicit

Sub ExcelToWord()

    Dim oWord As Object
    Dim oWordDoc As Object
    Dim rRange As Range

    On Error GoTo myOpen
    Set oWord = GetObject(, "Word.application.14")
    GoTo further
myOpen:
    Set oWord = CreateObject("Word.application.14")

further:
    oWord.Visible = True
    Set oWordDoc = oWord.Documents.Add

    Set rRange = ActiveSheet.UsedRange
    Range(rRange.Address).Copy
    oWord.Selection.Paste
    Application.CutCopyMode = False
    Set oWord = Nothing
    Set oWordDoc = Nothing
    
End Sub

In the second snippet, I will do the same, but I will use the function Selection.TypeText, in which one may type whatever he wants. With this function, I will write the name of the Excel file and the current date.

This is done with the following:

    With oWord.Selection
        .TypeText Text:="File name: " & ActiveWorkbook.Name
        .TypeParagraph
        .TypeText Text:="from " & Format(Now(), "dd-mmm-yyyy")
        .TypeParagraph
    End With

The whole code goes here:

Sub FurtherExcelToWord()

    Dim oWord As Object
    Dim oWordDoc As Object
    Dim i As Integer

    Sheets("Sheet1").Activate
    i = ActiveSheet.UsedRange.Rows.Count
    Range("A1:D" & i).Copy
    
    On Error GoTo myOpen
    
    Set oWord = GetObject(, "Word.application.14")
    GoTo further
    
myOpen:
    Set oWord = CreateObject("Word.application.14")
    
further:
    oWord.Visible = True
    Set oWordDoc = oWord.Documents.Add
    
    With oWord.Selection
        .TypeText Text:="File name: " & ActiveWorkbook.Name
        .TypeParagraph
        .TypeText Text:="from " & Format(Now(), "dd-mmm-yyyy")
        .TypeParagraph
    End With
    
    oWord.Selection.PasteSpecial link:=True
    Application.CutCopyMode = False
    Set oWord = Nothing
    Set oWordDoc = Nothing
    
End Sub

🙂

Enjoy it!