When I run the below code I get the following notification: "Microsoft Excel is waiting for another application to complete an OLE action". I'm not sure what's causing it. Below is the code I modified from "MacroPod" to fit my purposes. I think I'm doing something wrong in the ".openDataSource" portion. Not sure.
Excel Formula:
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
With wdApp
'Disable alerts to prevent an SQL prompt
.DisplayAlerts = wdAlertsNone
'Open the mailmerge main document
strFile = ActiveWorkbook.CustomDocumentProperties("fLabels").Value
Set wdDoc = .Documents.Open(strFile, ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False)
'wdApp.Visible = True
Debug.Print strWorkbookName
With wdDoc
With .MailMerge
'Define the mailmerge type
.MainDocumentType = wdFormLetters
'Define the output
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
'Connect to the data source
.OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
LinkToSource:=False, AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;Data Source=strWorkbookName;" & _
"Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM `nrPrintList`", _
SubType:=wdMergeSubTypeAccess
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
'Excecute the merge
.Execute
'Disconnect from the data source
.MainDocumentType = wdNotAMergeDocument
End With
'Close the mailmerge main document
.Close False
End With
'Restore the Word alerts
.DisplayAlerts = wdAlertsAll
'Display Word and the document
.Visible = True
End With