How to revert word object references in vba to earlier project library?

Question

There is a section of code I have written where a label merge is executed via Excel VBA. My client (2010) has an earlier version of Excel than me (2013) and is unable to execute that section due to having Word object references v 14 and me 15.

I believe the issue lies in early vs late binding? I have been trying to make the code work without the 15 reference checked in VBA, but no matter what variation of code I know possible, it still debugs when I try to save something that was previously declared an object. Im pretty sure that has something to do with it also. In the code, 3 seperate label templates are used to produce 3 label documents. There's no issue calling and closing the template, it's declared directly as its workbook path. The issue comes when the output of the merge, "wd" which I've set as an object, is attempted to be saved.

Sub RunMerge()
    ' Word constants

    Dim wd As Object
    Dim wdocSource As Object
    Dim wks As Worksheet
    Dim Worksheets
    Dim strWorkbookName As String

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set Worksheets = ActiveWorkbook.Sheets
    Set wks = ActiveSheet
    With ActiveWorkbook
        For Each wks In Worksheets

            Set wdocSource = wd.Documents.Open(ThisWorkbook.Path & "\Label Templates\PRODUCT Label Template_" & wks.Name & ".docx")

            strWorkbookName = ThisWorkbook.Path & "\Order Output\Order_Output_ " & Format(Date, "dd.mm.yyyy") & ".xlsx"

            wdocSource.MailMerge.MainDocumentType = wdFormLabels

            wdocSource.MailMerge.OpenDataSource _
                Name:=strWorkbookName, _
                AddToRecentFiles:=False, _
                Revert:=False, _
                Format:=wdOpenFormatAuto, _
                Connection:="Data Source=" & strWorkbookName, _
                SQLStatement:="SELECT * FROM [" & wks.Name & "$]"

            With wdocSource.MailMerge
                .Destination = wdSendToNewDocument
                .SuppressBlankLines = True
                 With .DataSource
                     .FirstRecord = wdDefaultFirstRecord
                     .LastRecord = wdDefaultLastRecord
                 End With
                 .Execute Pause:=False
            End With

            wd.SaveAs2 (ThisWorkbook.Path & "\Label Output\Label Output_" & wks.Name & "_" & Format(Date, "dd.mm.yyyy") & ".docx")
            wdocSource.Close SaveChanges:=False

        Next wks
    End With

    wd.Visible = True
    Set wdocSource = Nothing
    Set wd = Nothing
    Set wks = Nothing
    Set Worksheets = Nothing
End Sub

As soon as it hits wd.SaveAs2 there is an object reference error thrown, and if I change it there to ActiveDocument, or document or word.something or Dim it, Set it, put it in an end/with as an object, nothing works!!

macropod macropod · Accepted Answer · 2019-06-01T11:15:33

Your code is basically set up for use with late binding, but you've violated the late-binding principles by referring to built-in Word constants - and that necessitates the Word object library reference. Delete the Word object library reference and amend you code to be fully-workable with late binding (e.g. replace the built-in Word constants with their numeric values).

How to revert word object references in vba to earlier project library?

1 Answers