Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-31-2016, 06:49 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
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
  #2  
Old 03-31-2016, 07:32 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-01-2016, 03:50 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-01-2016, 04:15 AM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by JennEx View Post
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 View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-01-2016, 08:38 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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...
Reply With Quote
  #6  
Old 04-01-2016, 04:53 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-01-2016, 06:18 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 04-01-2016, 06:41 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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'?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 04-01-2016, 07:08 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Reply With Quote
  #10  
Old 04-01-2016, 07:49 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 04-02-2016, 06:19 AM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Mystery continues ...

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 ...

Reply With Quote
  #12  
Old 04-02-2016, 03:03 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 04-02-2016, 04:00 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Command failed ....

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...
Reply With Quote
  #14  
Old 04-02-2016, 04:30 PM
macropod's Avatar
macropod macropod is offline Applications Hanging During Merge Windows 7 64bit Applications Hanging During Merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hmm, I use that exact code on a test file and it worked just fine.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 04-02-2016, 05:13 PM
JennEx JennEx is offline Applications Hanging During Merge Windows XP Applications Hanging During Merge Office 2013
Competent Performer
Applications Hanging During Merge
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

:-( ...

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sharing data between Office applications mdavies Office 1 11-26-2013 02:55 PM
Applications Hanging During Merge All Office Applications lock up when saving abreeden Office 1 05-18-2012 08:02 PM
Applications Hanging During Merge 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft