excelvbams-word

Change a macro to use late bindings - Excel vba to create Word Table


I have tried (for the first time ever) to convert my macro to one that uses late bindings so that I don't have to add the Word Object Library to everyone's computer that uses the macro. From the bits of guidance I have found it appears that I just define all of the parts that refer to the Word Library as Objects and then Set the Object to be the reference that I was originally using. However it throws up an error saying that "ActiveX component can't create Object" whenever I try this. I feel I must be missing something really obvious but have no idea what.

My code;

Dim wdDoc As Object
Set wdDoc = CreateObject("Word.Document")
Dim wdTbl As Object
Set wdTbl = CreateObject("Word.Table")
Dim wdTblRows As Integer
Dim tblnum As Integer
Dim xlSht As Worksheet

tblnum = ActiveDocument.Tables.Count 'Counts number of tables in active document

Set wdTbl = ActiveDocument.Tables(tblnum) 'Set variable to the last table
Set xlSht = ActiveSheet 'Refers to this worksheet

wdTbl.Rows.Add 'Adds a row to the last table
wdTblRows = wdTbl.Rows.Count 'Counts how many rows there now are


With xlSht

    i = 1
    While i < 4
    
    wdTbl.Cell(wdTblRows, i).Range.Text = xlSht.Cells(2, i).Text 'Clunky but adds the text from the worksheet to the new row in the word document
    i = i + 1
    Wend
    

End With
End Sub

Solution

  • There are a few issues i have identified: ProgIDs are missing, ActiveDocument is not defined and your trying to create a Word.Table object directly. Here is the updated code.

    Sub UpdateWordTableLateBinding()
    
        Dim wdApp As Object
        Dim wdDoc As Object
        Dim wdTbl As Object
        Dim wdTblRows As Integer
        Dim tblnum As Integer
        Dim xlSht As Worksheet
        Dim i As Integer
    
        On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If wdApp Is Nothing Then
            Set wdApp = CreateObject("Word.Application")
        End If
        On Error GoTo 0
    
        If wdApp Is Nothing Then Exit Sub
    
        wdApp.Visible = True
    
        Set wdDoc = wdApp.ActiveDocument
        If wdDoc Is Nothing Then Exit Sub
    
        tblnum = wdDoc.Tables.Count
        If tblnum = 0 Then Exit Sub
    
        Set wdTbl = wdDoc.Tables(tblnum)
    
        wdTbl.Rows.Add
        wdTblRows = wdTbl.Rows.Count
    
        Set xlSht = ActiveSheet
    
        For i = 1 To 3
            wdTbl.Cell(wdTblRows, i).Range.Text = xlSht.Cells(2, i).Text
        Next i
    
    End Sub