VBA Mail Merge - Stopped working today after 14 years

[adamolivier]

I have been using this VBA process for 14 years with little to no adjustment, and today I tried using it on a new computer and it is getting stuck. It will open Word, but stops there and stays blank. No errors or bug alerts. What can I try to fix it? Is there a setting in Word or Excel that I need to adjust?

Sub RunDeed_OM_Texas_Merge()

Dim wd As Object
Dim wdocSource As Object

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
Set wdocSource = wd.Documents.Open("C:\Document Generator")

strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Data$`"

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

wd.Visible = True
wdocSource.Close SaveChanges:=False

Set wdocSource = Nothing
Set wd = Nothing

End Sub

 

[Candyman8019]

In your Visual Basic window check Tools | References and compare the references on the new computer to those on the old. Enable any required.

 

[adamolivier]

In your Visual Basic window check Tools | References and compare the references on the new computer to those on the old. Enable any required.

I did that, but no change. I am still having the same issue. To be clear, all of the same references were already selected.

 

[Candyman8019]

Assuming you have access to the data source from that new computer?

 

[adamolivier]

Assuming you have access to the data source from that new computer?

Yes, I even tried putting the source on the C drive and mapping there, but had the same issue.

 

[Candyman8019]

Have you tried stepping through the code with F8 and see where it's not working as expected?

 

[adamolivier]

Have you tried stepping through the code with F8 and see where it's not working as expected?

I am not familiar with using F8. Can you give me a quick explanation? I went to the code and pressed F8 and see how it steps through, but how do I tell where the issue is happening?
Also, I just realized that the code I pasted above does not have the proper file path. I pasted it before I completed the file path. I know my file path is good, but below is the actual code used right now.

Sub RunDeed_OM_Texas_Merge()

    Dim wd As Object
    Dim wdocSource As Object

    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
    Set wdocSource = wd.Documents.Open("\\tyl-srv-01\Acquisitions\Document Generator\Merge Template - MRWD.docx")

    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    wdocSource.MailMerge.MainDocumentType = wdFormLetters

    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `Data$`"

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

    wd.Visible = True
    wdocSource.Close SaveChanges:=False

    Set wdocSource = Nothing
    Set wd = Nothing

End Sub

 

[adamolivier]

Have you tried stepping through the code with F8 and see where it's not working as expected?

I think I figured out how F8 works. On my working machine I notice that the code runs for a couple of seconds once I hit the section below, and when finished my document is generated.

    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `Data$`"

However, on the machine that isnt working, F8 continues through this section and ulitmately opens Word at the code below. At that point I can see the Word doc with all of my merge fields, then as it takes the next steps that goes away and Word is blank without anything

    wdocSource.Close SaveChanges:=False

 

[Candyman8019]

Not sure what might be different between running it on the two computers. Do both have the same version of Office?

 

[Macropod]

Your code has the line:
Set wdocSource = wd.Documents.Open("C:\Document Generator")
but 'Document Generator' doesn't look like a valid filename. One would expect it to be 'Document Generator.doc' or 'Document Generator.docx'.