Simple example of Automation in Word? RRS feed

  • Question

  • Can someone show/describe a simple example of using Automation to import an Exel Worksheet into Word (Values only)?

    I always thought that importing a worksheet used Automation, but apparently it uses the Jet engine.  Thanks.

    Sunday, March 18, 2012 1:37 PM


  • Here is an example that uses Automation. It uses late binding, so you don't have to set a reference to the Excel object library.

    You should modify the workbook name, worksheet and range to suit your needs.

    Sub ImportFromExcel()
        Dim objXL As Object
        Dim objWB As Object
        Dim objWS As Object
        Dim blnStart As Boolean
        On Error Resume Next
        ' Try to get reference to running instance of Excel
        Set objXL = GetObject(Class:="Excel.Application")
        If objXL Is Nothing Then
            ' If not running, try to start Excel
            Set objXL = CreateObject(Class:="Excel.Application")
            If objXL Is Nothing Then
                MsgBox "Failed to start Excel", vbCritical
                Exit Sub
            End If
            blnStart = True
        End If
        On Error GoTo ErrHandler
        ' Open workbook
        Set objWB = objXL.Workbooks.Open(FileName:="C:\Excel\Test.xls")
        ' Reference to first worksheet
        Set objWS = objWB.Worksheets(1)
        ' Copy used range
        ' Paste into Word
        On Error Resume Next
        ' Clear Excel clipboard
        objXL.CutCopyMode = False
        ' Close workbook
        objWB.Close SaveChanges:=False
        ' Quit Excel if we started it
        If blnStart And Not objXL Is Nothing Then
        End If
        Exit Sub
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    Regards, Hans Vogelaar

    • Marked as answer by Bruce Song Thursday, March 29, 2012 6:15 AM
    Sunday, March 18, 2012 3:41 PM