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]

I believe that is part of the path.

 

[Macropod]

Even if that were so, there's still no valid document name for opening.

 

[adamolivier]

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'.

You are right, I mentioned that I fixed that above. Thanks for looking at it though.

 

[adamolivier]

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

I just confirmed both computers are running the same version/build

 

[adamolivier]

I just confirmed both computers are running the same version/build

FYI, I just set it up on a 3rd computer and it works. It is something specific to this particular machine. Driving me crazy.

 

[saboh12617]

Hello, maybe the MS Access is not setted up correctly on this PC. I believe the SQL query is ran by some dependencies of Access. Did you try to repair MS Office, or check if Access is installed?

(A similar issue Redirecting )

 

[adamolivier]

After some testing and moving of files, the issue appears to be that it only works when the Excel file is saved on a PC hard drive or server. When it is saved to a OneDrive or SharePoint folder that is synced with a desktop, the merge doesn't work. Any ideas on what could be going on?