Applications Hanging During Merge

JennEx · #1 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 · #2 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 · #3 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...

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