#16
|
||||
|
||||
The problem is most likely in the SQL statement. looking at it again, I suspect the itype & isubresp string variables need to be quoted. Try changing StrSQL to:
Code:
StrSQL As String = "SELECT * FROM [CORE$] WHERE [TYPE]=""" & itype & """ AND [SIG_CREW]= """ & isubresp & """" & _ " ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#17
|
|||
|
|||
So, unfortunately this last suggestion still resulted in the command failed error.
In an attempt to troubleshoot, I broke apart the 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 .Execute Pause:=False Up until then, the select table dialogue allowed me to select CORE as the datasource. The report populated (maybe prematurely) as it pumped out a page of 135 calculation errors. (ouch!). None of the mailmerge fields in the header or footer populated. But ... one problem a time! |
#18
|
||||
|
||||
Ok, so what happens if you delete 'Format:=wdOpenFormatAuto,'? If you get just the page of 135 calculation errors, but the right record count, the VBA code would appear to be working correctly.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#19
|
|||
|
|||
Code:
.OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _ ReadOnly:=True, 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 It is with this that the mysterious select table appears. When I had been testing, when this portion was not included, the line in green providing me with the select table dialogue in which I was able to select CORE. |
#20
|
||||
|
||||
OK, let's try to simplify things a bit, then build up one step at a time towards a solution. First, try the following:
Code:
Sub Test() Dim objWord As Object, oDoc As Object Dim StrSQL As String, fName As String, StrSrc As String Const wdSendtToNewDocument = 0 Const wdDirectory = 3 Const wdMergeSubTypeAccess = 1 Const wdOpenFormatAuto = 0 StrSQL = "SELECT * FROM [CORE$]" 'StrSQL = "SELECT * FROM [CORE$] " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=DR " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""""DR"""" " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" '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\Aug-30 (Sun) schedule_3.xlsx" fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx" 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] |
#21
|
|||
|
|||
Hi Paul, again I continue to thank you. If I have followed your instructions properly, using only one of the SQL options at a time, these are the results.
With: Code:
StrSQL = "SELECT * FROM [CORE$] With just: Code:
StrSQL = "SELECT * FROM [CORE$] " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" With: Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=DR " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" Same thing with ... Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""""DR"""" " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=[" & itype & "] AND [SIG_CREW]= [" & isubresp & "]" & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" Within a few minutes, with the Select Table box open, I will eventually get the "Waiting to complete an OLE process" message. |
#22
|
||||
|
||||
Quote:
Quote:
"ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" Do the START, COMPLEX & UNIT fields you're trying to order by exist in the data source (Aug-30 (Sun) schedule_3.xlsx)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#23
|
||||
|
||||
The field calculation errors suggest one or more fields referred to in your mailmerge main document don't exist in the data source (Aug-30 (Sun) schedule_3.xlsx).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#24
|
|||
|
|||
That (not unusual in this case) is odd. I created the merge document from scratch and used only those fields that were available to me in the dropdown from the datasource. The mailmerge works flawlessly when done manually.
My data holds different flavours of data, each one with a different report. ie DR has one, DT has one, FR etc. We've been trying to create a record for DR RPL. Maybe I will try a FR RPL (different report, different SQL) and see if I get similar problems. That may help isolate the merge document as the culprit. I'll report back. In the meantime, with my other post begging for help, I will send you the data source. Perhaps with that something unusual will jump out. Thanks Paul. |
#25
|
|||
|
|||
Paul, if you would be so kind, please consider the attached documents.
I've attached Reports for 'type' DR and FR, as well as the datasource for Aug-30 (Sun_ schedule_3.xlsx. The query uses this datasource filtering by type (DR for the DRv15 report; FR for the FRv15 report) and whatever sig_crew (HPE, HPL, RPE, RPL, WPE, WPL, CUE or CUL). The specific report and query is selected by the user in a user form based on the appropriateness. ie if there are FRs for RPL the button becomes active allowing the user to select and launch the merge for that combination. I was unable to make any difference between the DR and FR reports. Here is my current code for which I last left off on ... Code:
Sub merge2(ByVal i As Long, ByVal ws_vh As Object) Dim objWord As Object, oDoc As Object Dim StrSQL As String, fName As String, StrSrc As String Const wdSendtToNewDocument = 0 Const wdDirectory = 3 Const wdMergeSubTypeAccess = 1 Const wdOpenFormatAuto = 0 'StrSQL = "SELECT * FROM [CORE$]" 'StrSQL = "SELECT * FROM [CORE$] " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=DR " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""""DR"""" " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC" 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\Aug-30 (Sun) schedule_3.xlsx" fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx" 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 |
#26
|
||||
|
||||
Hi Jenn,
The following update to the test macro runs fine, for all versions of StrSQL: Code:
Sub Test() Dim objWord As Object, oDoc As Object Dim StrSQL As String, fName As String, StrSrc As String Const wdSendtToNewDocument = 0 Const wdSendToPrinter = 1 Const wdFormLetters = 0 Const wdDirectory = 3 Const wdMergeSubTypeAccess = 1 Const wdOpenFormatAuto = 0 Const itype As String = "DR" Const isubresp As String = "CUL" StrSrc = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\Aug-30 (Sun) schedule_3.xlsx fName = "H:\PWS\Parks\Parks Operations\Sports\Sports15\REPORTS\DR15v1.docx" StrSQL = "SELECT * FROM [CORE$]" 'StrSQL = "SELECT * FROM [CORE$] " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""DR"" " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [SIG_CREW]=""CUL"" " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""DR"" AND [SIG_CREW]=""CUL"" " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 'StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 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 = wdFormLetters .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 As for the mailmerge main document, you really do need to do some work on those documents. They have a bunch of incomplete formula fields (only the ones in cells D2-D4 actually do anything meaningful); and one even references a field that doesn't exist in the data source. The latter is inside one of numerous unnecessary SET fields, most of which are also incomplete. Clean those up and the field calculation errors will go away. See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#27
|
|||
|
|||
Hi Paul, I so appreciate your help. I haven't sat down yet to fully digest things because, well ... you have me confused. LOL.
Quote:
Quote:
I'm excited to see how this works. |
#28
|
||||
|
||||
Quote:
StrSQL = "SELECT * FROM [CORE$] " & _ "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#29
|
|||
|
|||
Hey Paul!
Hi Paul, I have started to test with your latest code now that I think I have cleaned up my report. Looks like things are finally moving forward as hoped, with all thanks to your dedication in helping solve the problem. I am still ashamed that one letter caused so much grief. My apologies for not being very thorough, but it all starts to look the same after a while. I am using this sql statement: Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" 1) I need directory type. Right now I getting letter type merges. Where do I need to change that? 2) I am having a heck of a time establishing the time fields in a h:mm AM/PM time format despite having included switches. The date switches worked fine, just not the time. { MERGEFIELD E1_Start \@"h:mm AM/PM" } - with ALT-F9 3) I am getting duplicate reports being run, but this may not be related to the module you helped me with. |
#30
|
||||
|
||||
If you want a Directory merge, change:
.MainDocumentType = wdFormLetters to: .MainDocumentType = wdDirectory Do note, however, that means you'll lose your individual page headers & footers - a Directory merge can have only one of each. An alternative would be to perform a Letter merge (as per the current code), then delete the Section breaks post-merge. The outcome will be the same, though. You can do that by inserting the following code after .DisplayAlerts = True Code:
With .ActiveDocument If .Sections.Count > 1 Then For Each HdFt In .Sections(.Sections.Count).Headers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Headers(HdFt.Index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next For Each HdFt In .Sections(.Sections.Count).Footers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Footers(HdFt.Index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next End If Do While .Sections.Count > 1 .Sections(1).Range.Characters.Last.Delete DoEvents Loop .Range.Characters.Last.Delete End With The generation of duplicate reports suggests your code is being called twice.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
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 |
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 |