Applications Hanging During Merge

JennEx · #1 03-31-2016, 06:49 PM

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?

macropod · #2 03-31-2016, 07:32 PM

The code looks alright but, if the document has been saved as a mailmerge main document, your code will stop, waiting for the SQL prompt to be responded to. You can circumvent that with:

Code:

With objWord
    .DisplayAlerts = False
    Set oDoc = objWord.Documents.Open(FileName:=fName, ConfirmConversions:=True, _
        ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
    StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
    With oDoc
        With .MailMerge
            .MainDocumentType = wdDirectory
            .Destination = wdSendtToNewDocument
            .SuppressBlankLines = True
            .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
            .Execute Pause:=False
        End With
        .Close False
    End With
    .DisplayAlerts = True
End With

It might also be worthwhile checking what's being passed via fName and that that file actually contains the relevant mergefields.

JennEx · #3 04-01-2016, 03:50 AM

Thank you Paul ,

I do believe that to be the case ... it was saved as a mail merge document with the SQL as you pointed out. Thank you for the code solution. I recall last time I did a similar project I had a heck of a time saving the merge document without the SQL for some reason to get the code to work. I am hoping this code will keep that challenge to a minimum.

I noticed in your code you removed '.MainDocumentType = wdNotAMergeDocument'. I assume it's unnecessary in this application?

And, I have just stumbled on the fact that my data source has two columns labelled as 'TYPE', which is one of the SQL criteria. Would this contribute to the problem? I know it's not good but wondering how crippling a problem it will create in the mail merge process.

macropod · #4 04-01-2016, 04:15 AM

Quote:

Originally Posted by JennEx

I noticed in your code you removed '.MainDocumentType = wdNotAMergeDocument'. I assume it's unnecessary in this application?

Since the code closes your mailmerge main document without saving the changes the code makes to it, that line is unnecessary.

The '.DisplayAlerts = False' line suppresses the mailmerge SQL prompt. Be aware this also causes the document to revert to an ordinary one - which is also why you then need the code you already had for setting the document type, opening the data source, etc. And, of course, one should restore with '.DisplayAlerts = True' before exiting, otherwise important prompts might not be given post-merge.

Quote:

Originally Posted by JennEx

I have just stumbled on the fact that my data source has two columns labelled as 'TYPE', which is one of the SQL criteria. Would this contribute to the problem?

I may but, even if it doesn't, you might find it pulling data from the wrong one! Better to have unique names for all fields.

JennEx · #5 04-01-2016, 08:38 AM

Hi Paul, thank you for your continued support. It's very much appreciated.

I'm continuing to get that error with Excel despite the recommended changes.

Code:

Sub Merge2(ByVal i As Long, ByVal ws_vh As Object)
    Dim ws_th As Worksheet
    Set ws_th = Workbooks("Sports15b.xlsm").Worksheets("TEMP_HOLD")
    itype = Right(ws_th.Range("A" & i + 1), 2)
    isubresp = Left(ws_th.Range("A" & i + 1), 3)
    
    If itype = "DR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx"
    ElseIf itype = "DT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DT15v1.docx"
    ElseIf itype = "FR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FR15v1.docx"
    ElseIf itype = "FT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FT15v1.docx"
    ElseIf itype = "CR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CR15v1.docx"
    Else
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CT15v1.docx"
    End If
      
    Set objWord = CreateObject("Word.Application")
    With objWord
        .DisplayAlerts = False
        .Visible = True
        Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=True, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
        StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
        MsgBox StrSrc
        With oDoc
            With .MailMerge
                .MainDocumentType = wdDirectory
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .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
                .Execute Pause:=False
            End With
            .Close False
        End With
        .DisplayAlerts = True
    End With
End Sub

I did change the header in the data source to take care of the duplicate 'type' issue.
StrSrc is reporting back the correct excel datafile and path.
fname is reporting correct, and the file it's calling exists.

In case it matters, the source (Excel) datafile is open at this point, but hidden.

It beats me...

macropod · #6 04-01-2016, 04:53 PM

I suspect your 'ConfirmConversions' implementation may be an issue. Try using:

Code:

        Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)

or:

Code:

        Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)

JennEx · #7 04-01-2016, 06:18 PM

Paul, with your second recommendation, the correct merge document became visible and Word didn't hang. It did however prompt me to select a table. The field to select a table was blank.

The workbook to what it was referring to was weird. It is a workbook I didn't create, Excel created it on it's own putting it in the directory on my main Excel document. It was with an '.xls' extension and can't be opened.

In due time, the 'Waiting for OLE' message appears followed by a "Command failed" error with the line in red.

Code:

Sub Merge2(ByVal i As Long, ByVal ws_vh As Object)
    Dim ws_th As Worksheet
    Set ws_th = Workbooks("Sports15b.xlsm").Worksheets("TEMP_HOLD")
    itype = Right(ws_th.Range("A" & i + 1), 2)
    isubresp = Left(ws_th.Range("A" & i + 1), 3)
    
    If itype = "DR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx"
    ElseIf itype = "DT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DT15v1.docx"
    ElseIf itype = "FR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FR15v1.docx"
    ElseIf itype = "FT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FT15v1.docx"
    ElseIf itype = "CR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CR15v1.docx"
    Else
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CT15v1.docx"
    End If
      
    Set objWord = CreateObject("Word.Application")
    With objWord
        .DisplayAlerts = False
        .Visible = True
        Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
        StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
        MsgBox StrSrc
        With oDoc
            MsgBox "Type: " & itype & Chr(13) & "Sig_Crew: " & isubresp
            With .MailMerge
                .MainDocumentType = wdDirectory
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .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
                .Execute Pause:=False
            End With
            .Close False
        End With
        .DisplayAlerts = True
    End With
End Sub

macropod · #8 04-01-2016, 06:41 PM

So how did Excel create the workbook? I don't see any code for that.

Also, is whatever is being returned by ws_vh.Range("B4") a valid workbook name (including the extension) and does that workbook have a sheet named 'CORE'?

JennEx · #9 04-01-2016, 07:08 PM

Quote:

Originally Posted by macropod

So how did Excel create the workbook? I don't see any code for that. Also, is whatever is being returned by ws_vh.Range("B4") a valid workbook name (including the extension) and does that workbook have a sheet named 'CORE'?

I was hoping you might be able to answer that LOL! That never happened until I made the changes you the set odoc statement. {Not saying its the cause but may have allowed us to uncover a problem.) DATA1 is the folder in which the data source file exists. In my testing, ws_vh.range("B4") = Aug-30 (Sun) schedule_3.xlsx , which exists in folder DATA1. This file has a worksheet called "CORE" in addition to another called "STAFF"

StrSrc = the path of the file ("H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\") and the file name from ws_vh.Range("B4") resulting in "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\Aug-30 (Sun) schedule_3.xlsx

I have a msgbox following the creation of StrSrc to confirm that the the path and file (ie the value of StrSrc is correct, and as far as I can see it is.

When I created this mail merge document, in the selecting recipients stage, I had to select the path, file and worksheet of the data source before I could continue populating the mail merge document with fields. That all worked as planned. To test manually, I set an sql to filter out only DR as type.

macropod · #10 04-01-2016, 07:49 PM

A datasource described as:
H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1.xls
suggests all there was in ws_vh.Range("B4") was ".xls"
That said, I can't see how the path separator (\) after DATA1 could get deleted.

Try:

Code:

Sub Merge2(ByVal i As Long, ByVal ws_vh As Object)
    Dim objWord As Object, oDoc As Object, oDoc2 As Object
    Dim myPath As String, fName As String, StrSrc As String
    Dim itype As String, isubresp As String, StrSQL As String
    Dim ws_th As Worksheet
    Const wdSendtToNewDocument = 0
    Const wdSendToPrinter = 1
    Const wdFormLetters = 0
    Const wdDirectory = 3
    Const wdNotAMergeDocument = -1
    Const wdMergeSubTypeAccess = 1
    Set ws_th = Workbooks("Sports15b.xlsm").Worksheets("TEMP_HOLD")
    itype = Right(ws_th.Range("A" & i + 1), 2)
    isubresp = Left(ws_th.Range("A" & i + 1), 3)
    StrSQL = "SELECT * FROM `CORE$` WHERE [TYPE]='" & itype & "' AND [SIG_CREW]= '" & isubresp & "' ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
    StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
 
    If itype = "DR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx"
    ElseIf itype = "DT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DT15v1.docx"
    ElseIf itype = "FR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FR15v1.docx"
    ElseIf itype = "FT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FT15v1.docx"
    ElseIf itype = "CR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CR15v1.docx"
    Else
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CT15v1.docx"
    End If
 
    MsgBox "Mailmerge Main Document: " & fName
    MsgBox "Data Source: " & StrSrc & vbCr & "Type: " & itype & vbCr & "Sig_Crew: " & isubresp & vbCr & "SQL: " & StrSQL
 
    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 = wdDirectory
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .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:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
                .Execute Pause:=False
            End With
            .Close False
        End With
        .DisplayAlerts = True
    End With
End Sub

JennEx · #11 04-02-2016, 06:19 AM

Hi Paul ... I cannot say how appreciative I am of the effort you have put in to try to solve this mystery. The mystery continues as the results remain consistent.

Pictures are worth many words, so I hope they are helpful.

First ... the contents of vs_vh.range("B4"). There are no hidden spaces or characters according to the len formula applied to B4 in cel C4 (28 char)

Here is an image of the Datasource file through the eyes of Windows Explorer. The preview pane shows the worksheet 'CORE" for which the mailmerge refers.

As we go through the code, we get these messages ...
Confirmation of the mail merge document:

Confirmation of the data source and sql ...

Then, we get the weird 'select source' with workbook reference to 'Data1.xls"

With 'DATA1.xls' as seen in Windows Explorer... (which I manually or intentionally put there ... excel or word must have created it at some point in the SQL line. As you can see ... the worksheet name is crazy and there is no data)

And within minutes ...

macropod · #12 04-02-2016, 03:03 PM

Hi Jenn,

It occurs to me the SQL statement is flawed. In a couple of place, you have ' whereas the SQL requires `, which can be replaced with square brackets. Similarly, the syntax for the .OpenDataSource code is flawed, especially at 'Data Source=StrSrc'. Try:

Code:

Sub Merge2(ByVal i As Long, ByVal ws_vh As Object)
    Dim objWord As Object, oDoc As Object, oDoc2 As Object
    Dim myPath As String, fName As String, StrSrc As String
    Dim itype As String, isubresp As String, StrSQL As String
    Dim ws_th As Worksheet
    Const wdSendtToNewDocument = 0
    Const wdSendToPrinter = 1
    Const wdFormLetters = 0
    Const wdDirectory = 3
    Const wdNotAMergeDocument = -1
    Const wdMergeSubTypeAccess = 1
    Set ws_th = Workbooks("Sports15b.xlsm").Worksheets("TEMP_HOLD")
    itype = Right(ws_th.Range("A" & i + 1), 2)
    isubresp = Left(ws_th.Range("A" & i + 1), 3)
    StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=[" & itype & "] AND [SIG_CREW]= [" & isubresp & "]" & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
    StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & ws_vh.Range("B4")
    
    If itype = "DR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx"
    ElseIf itype = "DT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DT15v1.docx"
    ElseIf itype = "FR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FR15v1.docx"
    ElseIf itype = "FT" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\FT15v1.docx"
    ElseIf itype = "CR" Then
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CR15v1.docx"
    Else
        fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\CT15v1.docx"
    End If
    
    MsgBox "Mailmerge Main Document: " & fName
    MsgBox "Data Source: " & StrSrc & vbCr & "Type: " & itype & vbCr & "Sig_Crew: " & isubresp & vbCr & "SQL: " & StrSQL
      
    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 = wdDirectory
                .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

JennEx · #13 04-02-2016, 04:00 PM

Hi Paul ...

No hanging this time, just a "Command failed" error with

Code:

.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

Hopefully this is a step forward for us...

macropod · #14 04-02-2016, 04:30 PM

Hmm, I use that exact code on a test file and it worked just fine.

JennEx · #15 04-02-2016, 05:13 PM

:-( ...

If it's stumping you, then it's certainly blowing my mind and is quite discouraging.

I cleared the SQL and redid the select recipients, saved the document and still a "Command Failed" error.

Manually ran the mailmerge document and edited the recipients according to our criteria and it worked (except my times were all decimal values ,,, which I originally had to change the datasource to DDE to overcome).

I previewed the results with all 6 records (of my sample) being merged without problem.

It wouldn't have anything to do with the media that the datasource is stored on would it?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sharing data between Office applications	mdavies	Office	1	11-26-2013 02:55 PM
All Office Applications lock up when saving	abreeden	Office	1	05-18-2012 08:02 PM
VB Applications?	RiverStyx	Office	1	05-30-2011 08:31 PM
Change skin color of applications?	Jack R	Office	0	07-04-2010 04:25 AM
How many Word applications are running GetObject	stevecarr	Word	0	01-09-2006 07:41 AM