View Single Post
 
Old 03-31-2016, 06:49 PM
JennEx JennEx is offline Windows XP Office 2013
Competent Performer
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Applications Hanging During Merge

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?
Reply With Quote