Hi Jenn,
The following update to the test macro runs fine, for all versions of StrSQL:
Code:
Sub Test()
Dim objWord As Object, oDoc As Object
Dim StrSQL As String, fName As String, StrSrc As String
Const wdSendtToNewDocument = 0
Const wdSendToPrinter = 1
Const wdFormLetters = 0
Const wdDirectory = 3
Const wdMergeSubTypeAccess = 1
Const wdOpenFormatAuto = 0
Const itype As String = "DR"
Const isubresp As String = "CUL"
StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\Aug-30 (Sun) schedule_3.xlsx
fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx"
StrSQL = "SELECT * FROM [CORE$]"
'StrSQL = "SELECT * FROM [CORE$] " & _
"ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""DR"" " & _
"ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
'StrSQL = "SELECT * FROM [CORE$] WHERE [SIG_CREW]=""CUL"" " & _
"ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""DR"" AND [SIG_CREW]=""CUL"" " & _
"ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _
"ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
Set objWord = CreateObject("Word.Application")
With objWord
.DisplayAlerts = False
.Visible = True
Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _
ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
With oDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendtToNewDocument
.SuppressBlankLines = True
.OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _
ReadOnly:=True, Format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Execute Pause:=False
End With
.Close False
End With
.DisplayAlerts = True
End With
End Sub
As I suspected, the START, COMPLEX & UNIT fields don't all exist in the data source; START should have been STARTS!
As for the mailmerge main document, you really do need to do some work on those documents. They have a bunch of incomplete formula fields (only the ones in cells D2-D4 actually do anything meaningful); and one even references a field that doesn't exist in the data source. The latter is inside one of numerous unnecessary SET fields, most of which are also incomplete. Clean those up and the field calculation errors will go away. See attached.