I have an Excel application that creates directory style merged documents using an excel database as it's source ...
From Excel VBA
Code:
Dim objWord As Object, oDoc As Object, oDoc2 As Object
Dim myPath As String, fName As String, StrSrc As String
Dim itype As String
Dim isubresp As String
Const wdSendtToNewDocument = 0
Const wdSendToPrinter = 1
Const wdFormLetters = 0
Const wdDirectory = 3
Const wdNotAMergeDocument = -1
Const wdMergeSubTypeAccess = 1
Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=True, _
ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
'StrSrc = ThisWorkbook.FullName
StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
MsgBox StrSrc
With oDoc
With .MailMerge
.MainDocumentType = wdDirectory
.OpenDataSource _
Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
"Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM `CORE$` WHERE [TYPE]='" & itype & "' AND [SIG_CREW]= '" & isubresp & "' ORDER BY [Start] ASC, [COMPLEX] ASC, [UNIT] ASC", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendtToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = .RecordCount
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
.Close False
End With
When the run reaches the line in red, both Word and Excel become unresponsive. Periodically, it will break with a message ... "Microsoft Excel is waiting for another application to complete an OLE action."
I suspect there is something wrong with my SQL Query.
StrSrc = ""H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\Aug-30 (Sun) schedule_3.xlsx"
itype = "DR"
isubresp="HPL"
Data from worksheet "CORE" is queried.
Order by columsn START, COMPLEX, UNIT
Is anyone able to see the bug in this line?