none
Dynamically importing Excel Data to Word with VBA RRS feed

  • Question

  • I have some VBA code in an Excel Module which works fine for copying and pasting data into a Word document. The problem is I need the Data going into Word to be dynamically linked to the Excel sheet. Any ideas?

    Sub ActivateWord()
    Worksheets("FIN_DATA_1-1").Range("AG2:AH32").Copy
    'Declare Object variables for the Word application and document.
    Dim WdApp As Object, wddoc As Object
    'Declare a String variable for the example document’s
    'name and folder path.
    Dim strDocName As String
    'On Error statement if Word is not already open.
    On Error Resume Next
    'Activate Word if it is already open.
    Set WdApp = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Err.Clear
    'Create a Word application if Word is not already open.
    Set WdApp = CreateObject("Word.Application")
    End If
    'Make sure the Word application is visible.
    WdApp.Visible = True
    'Define the strDocName String variable.
    strDocName = "G:\Manila LRT Line 1\World Bank\Management Reporting\Grantors_Report.docx"
    'Check the directory for the presence of the document
    'name in the folder path.
    'If it is not recognized, inform the user of that
    'fact and exit the macro.
    If Dir(strDocName) = "" Then
    MsgBox "The file " & strDocName & vbCrLf & _
    "was not found in the folder path" & vbCrLf & _
    "G:\Manila LRT Line 1\World Bank\Management Reporting\.", _
    vbExclamation, _
    "Sorry, that document name does not exist."
    Exit Sub
    End If
    Wednesday, October 12, 2016 7:40 AM

Answers

  • Hi PaulDHC,

    According to your description, you could use PasteSpecial method that you can control the format of the pasted information and (optionally) establish a link to the source file (for example, a Microsoft Excel worksheet). 
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
            wdInLine, DisplayAsIcon:=False

    For more information, click here to refer to PasteSpecial Method

    Thanks for your understanding.
    Thursday, October 13, 2016 2:53 AM