Sub ActivateWordTransferData()
Worksheets(“Sheet1”).Range(“A1:D4”).Copy
‘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
Err.Clear
‘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
wdapp.Activate
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)
wddoc.Activate
wddoc.Range.Paste
wddoc.Save
wdapp.Quit
‘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