Import Data into Word from Excel Using VBA

How we can import data into Word from Excel using VBA quickly and easily.

The first thing we need to do is open the MS Word application. So there are four situations to be taken care of while working wit MS Word from Excel:

  • The MS Word application may not be open
  • MS Word may be open but the document we wish to access may not be open
  • The Word file or document that we wish to access may be open
  • The Word file that we wish to open may have been deleted, renamed or moved to another folder and therefore not ‘exist’ for our VBA code
  • If the MS Word file is not found we need to tell the user that the file is not available

Now once we open the Word file we can paste the copied range into it. After that the Word document should be saved and finally the application should be closed. Also we need to take care of error handling otherwise the process of working wit Word from Excel to import data between the Microsoft applications causes lot of pain. Often you will get messages that create more confusion than clarity.

Watch the training video before you dive into the VBA code:

Watch the video on YouTube.

Sub ActivateWordTransferData()
‘Declare Object variables for the Word application and file or document
Dim wdapp As Object, wddoc As Object
‘Declare a String variable for the file name and folder path
Dim strdocname As String
‘error handling is extremely important in making Excel work with Word
On Error Resume Next
‘GetObject needs two parameters. The first is optional
Set wdapp = GetObject(, “Word.Application”)
If Err.Number = 429 Then
‘we create a new instance of MS Word
Set wdapp = CreateObject(“Word.Application”)
End If
‘Our application is made visible
wdapp.Visible = True
strdocname = “C:\our-inventory\inventory-report.docx”
‘we create a friendly message for the user
If Dir(strdocname) = “” Then
MsgBox “The file ” & strdocname & vbCrLf & “was not found ” & vbCrLf & “C:\our-inventory\.”, vbExclamation, “The document does not exist.”
Exit Sub
End If
‘we activate our MS Word instance
Set wddoc = wdapp.Documents(strdocname)
‘we open MS Word file if it is not open
If wddoc Is Nothing Then Set wddoc = wdapp.Documents.Open(strdocname)
‘Here we free memory allocated to our two object variables
Set wddoc = Nothing
Set wdapp = Nothing
‘The following line of code removes the selection of range A1:D4 in Excel
Application.CutCopyMode = False
End Sub

Download sample file by clicking on the Excel: