VBA – Transfer data from Excel to Word

Posted on November 1, 2014 by

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

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

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

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!