#1
|
|||
|
|||
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 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? |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
Quote:
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. 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] |
#5
|
|||
|
|||
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 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... |
#6
|
||||
|
||||
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) Code:
Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=False, _ ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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 |
#8
|
||||
|
||||
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] |
#9
|
|||
|
|||
Quote:
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. |
#10
|
||||
|
||||
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] |
#11
|
|||
|
|||
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 ... |
#12
|
||||
|
||||
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] |
#13
|
|||
|
|||
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 |
#14
|
||||
|
||||
Hmm, I use that exact code on a test file and it worked just fine.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
:-( ...
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 |