Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 04-02-2016, 07:50 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 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"
PS: VBA-encoded SQL statements are not my forte.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #17  
Old 04-03-2016, 05:09 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

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
I first encountered the command failed error with the piece highlighted in red.

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!
Reply With Quote
  #18  
Old 04-03-2016, 05:22 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

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]
Reply With Quote
  #19  
Old 04-03-2016, 06:24 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

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
With 'Format:=wdOpenFormatAuto' removed, things seem to work up until the line in blue.

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.
Reply With Quote
  #20  
Old 04-03-2016, 05:35 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

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
If that works, try un-commenting each of the StrSQL = lines in turn, to see which ones work. You should comment-out each line before testing the next one.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #21  
Old 04-03-2016, 06:34 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

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$]
Word just kept opening mail merge documents over and over, mergiing without headers and footers and reports of 135 calculation errors (there are only 31 records in my database). I had to <CTRL><ALT><DEL> to end Excel to stop the Word reports from being created non stop.

With just:
Code:
StrSQL = "SELECT * FROM [CORE$] " & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
Received the mysterious select table prompt with the DATA1.xls worksheet.

With:
Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=DR " & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
Received the mysterious select table prompt with the DATA1.xls worksheet.

Same thing with ...
Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=""""DR"""" " & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
and lastly, with
Code:
StrSQL = "SELECT * FROM [CORE$] WHERE [TYPE]=[" & itype & "] AND [SIG_CREW]= [" & isubresp & "]" & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
Same thing. Select table from workbook DATA1.xls

Within a few minutes, with the Select Table box open, I will eventually get the "Waiting to complete an OLE process" message.
Reply With Quote
  #22  
Old 04-03-2016, 07: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

Quote:
Originally Posted by JennEx View Post
With:
Code:
StrSQL = "SELECT * FROM [CORE$]
Word just kept opening mail merge documents over and over, mergiing without headers and footers and reports of 135 calculation errors (there are only 31 records in my database). I had to <CTRL><ALT><DEL> to end Excel to stop the Word reports from being created non stop.
I can't see how it's possible for the 'over and over' to occur, as there's no looping in the code - just a single merge to a new file. All you should have ended up with is a single file with outputs for your 31 records. Did you just run the macro on its own, or did you call it from another one? The lack of separate headers and footers is because you're doing a Directory merge, which has only a single Section and hence, a single header & footer.
Quote:
Originally Posted by JennEx View Post
With just:
Code:
StrSQL = "SELECT * FROM [CORE$] " & _
              "ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
Received the mysterious select table prompt with the DATA1.xls worksheet.
Evidently, then, there's a problem with this part of the SQL:
"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]
Reply With Quote
  #23  
Old 04-03-2016, 10:43 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

Quote:
Originally Posted by JennEx View Post
135 calculation errors
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]
Reply With Quote
  #24  
Old 04-04-2016, 05:32 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

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.
Reply With Quote
  #25  
Old 04-04-2016, 07:57 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

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
Attached Files
File Type: docx DR15v1.docx (386.3 KB, 7 views)
File Type: docx FR15v1.docx (377.6 KB, 7 views)
File Type: xlsx Aug-30 (Sun) schedule_3.xlsx (25.4 KB, 7 views)
Reply With Quote
  #26  
Old 04-04-2016, 11:07 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,

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 I suspected, the START, COMPLEX & UNIT fields don't all exist in the data source; START should have been STARTS!

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.
Attached Files
File Type: docx DR15v1.docx (380.3 KB, 6 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #27  
Old 04-05-2016, 04:10 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, I so appreciate your help. I haven't sat down yet to fully digest things because, well ... you have me confused. LOL.

Quote:
START, COMPLEX & UNIT fields don't all exist in the data source; START should have been STARTS!
Am I misunderstanding this statement?


Quote:
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.
I was obviously not seeing the same content you had seen in the reports. After discovering that ALT F9 revealed a whole dark underworld, it is quite evident of the errors!! Some of those formulae I have NO Idea how they got there, but are certainly unnecessary.

I'm excited to see how this works.
Reply With Quote
  #28  
Old 04-05-2016, 06:22 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
Hi Paul, I so appreciate your help. I haven't sat down yet to fully digest things because, well ... you have me confused. LOL.



Am I misunderstanding this statement?
Well, STARTS isn't the same as START, which is what the code you supplied had. That is why the code crashed even when using just:
StrSQL = "SELECT * FROM [CORE$] " & _
"ORDER BY [START] ASC, [COMPLEX] ASC, [UNIT] ASC"
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #29  
Old 04-05-2016, 06:53 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

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"
The merges seem to be going well, except for:

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.
Reply With Quote
  #30  
Old 04-05-2016, 07:37 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

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
As for the AM/PM, I'm not having any trouble here. See attached.

The generation of duplicate reports suggests your code is being called twice.
Attached Files
File Type: docx DR15v1.docx (380.4 KB, 12 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
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 04:44 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