Problems With MergeField Switches

macropod · #46 04-10-2013, 05:39 AM

Word sometimes adds the \* MERGEFORMAT switch out of sheer perversity(!), usually after you've changed the format of the field. I'd recommend a Find/Replace to delete all occurrences - they're more likely to cause problems than to be of any benefit.

JennEx · #47 04-15-2013, 06:21 PM

Hi Paul ... are you up to helping me with a few problems?

As you know, I am using Excel VBA to launch Word and the appropriate mailmerge document ("report"). Thanks to you ... preliminary efforts have proved most positive.

Here is my macro to do this ...

Code:

Sub Merge2()
    Dim wshfront As Worksheet
    Set wshfront = Worksheets("Front")
    itype = Worksheets("Front").Range("E12")
    isubresp = Worksheets("Front").Range("G12")
 
    Unload UserForm4
 
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
 
    itype = Worksheets("varhold").Range("W1")
 
    If itype = "DR" Then
        fName = "u:\Sports13\Reports\DR\v9\DRv9.docx"
    ElseIf itype = "DT" Then
        MsgBox "Report not created"
        'fName = "u:\Sports13\Reports\DR\v9\" & Worksheets("Front").Range("I14")
    ElseIf itype = "FR" Then
        fName = "u:\Sports13\Reports\FR\v9\FRv9.docx"
    ElseIf itype = "FT" Then
        MsgBox "Report not created"
        'fName = "u:\Sports13\Reports\DR\v9\" & Worksheets("Front").Range("I14")
    ElseIf itype = "CR" Then
        MsgBox "Report not created"
        'fName = "u:\Sports13\Reports\DR\v9\" & Worksheets("Front").Range("I14")
    Else
        MsgBox "Report not created"
        'fName = "u:\Sports13\Reports\DR\v9\" & Worksheets("Front").Range("I14")
    End If
 
    Set oDoc = objWord.Documents.Open(Filename:=fName, ConfirmConversions:=True, _
        ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
    StrSrc = ThisWorkbook.FullName
    '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 `CONTROL_1$` WHERE [Type$]='" & itype & "' AND [SubResp]= '" & isubresp & "' ORDER BY [Start] ASC, [Facility B$] ASC, [Unit$] ASC", _
                SQLStatement1:="", SubType:=wdMergeSubTypeAccess
            If Worksheets("varhold").Range("V1") = "Edit" Then
               .Destination = wdSendtToNewDocument
           Else
               .Destination = wdSendToPrinter
           End If
            .SuppressBlankLines = True
            With .DataSource
              .FirstRecord = 1
              .LastRecord = .RecordCount
            End With
            .Execute Pause:=False
            .MainDocumentType = wdNotAMergeDocument
        End With
        .Close False
    End With
    Set oDoc2 = objWord.ActiveDocument
    myPath = "u:\Sports13\Workorders\" & Format(Worksheets("varhold").Range("A1"), "ddd dd-mmm-yy")
    If Len(Dir(myPath, vbDirectory)) = 0 Then MkDir myPath
    oDoc2.SaveAs myPath & "\" & (Worksheets("varhold").Range("A46").Value & "docx")
    AppActivate "Microsoft Excel"
    Set oDoc = Nothing: Set oDoc2 = Nothing: Set objWord = Nothing
End Sub

As you see, highlighted in green, I have what allows the user the option to edit the document (wdSendtToNewDocument) or send to printer (wdSendToPrinter)

1. Is it true I can't send a catalog mailmerge directly to the printer? "Run-time error '5661': You cannot send a catalog created by merging documents directly to mail, fax or a printer." :-(

I use a code in Excel to generate a list of reports as selected by the user. From the GUI from which the user selects their reports to process, a click of the "GO" button launches userform4.

Code:

Sub proceed23()
 
    'Written by Trebor76
    'Visit my website www.excelguru.net.au
 
    Dim strWrkSheetArray() As String 'Declares a dynamic array variable to hold the relevant sheet tabs.
    Dim intArrayCount As Integer
    Dim varWrkSheet As Variant
    Dim rngCell As Range
 
    For Each rngCell In Worksheets("varhold").Range("T3:T10")
        If Len(rngCell) > 0 Then
            intArrayCount = intArrayCount + 1
            ReDim Preserve strWrkSheetArray(1 To intArrayCount) 'Copy elements from the existing array to the new array
            strWrkSheetArray(intArrayCount) = Left(CStr(rngCell), 31) 'Maximum length of a tab is 31.
        End If
    Next rngCell
 
    If intArrayCount = 0 Then
        MsgBox "Nothing has been assigned to array. Check your data and try again."
        Exit Sub
    End If
 
    'Ark68, this is just to show how to use the 'strWrkSheetArray' variable
    For Each varWrkSheet In strWrkSheetArray
        Worksheets("varhold").Range("W1") = Right(varWrkSheet, 2)
        UserForm4.TextBox2.Value = varWrkSheet
        UserForm4.Show
        'MsgBox varWrkSheet
    Next varWrkSheet
 
End Sub

Userform4 has the buttons for the user to specify whether they prefer to 'edit' or 'print' the report. It populates the two variables referenced in sub Merge2 (above) needed for Merge2 to work. It also launches the procedure to loop the user through each report merge in the cue.

2. Once Word has done it's thing with merge2, is it possible to resume the 'proceed23' macro in Excel to continue on through the cue? Recall proceed23 is meant to loop through the selected reports the user wishes to print.

Thanks ...

Jenn

[code]

macropod · #48 04-15-2013, 07:41 PM

Quote:

1. Is it true I can't send a catalog mailmerge directly to the printer?

Yes. However, since this is a macro driven process, the overhead to generate a document for all merges, then print it (using the .Printout method) and delete it without saving if the user chose a 'Print' option, is trivial.

Quote:

2. Once Word has done it's thing with merge2, is it possible to resume the 'proceed23' macro in Excel to continue on through the cue?

If I understand your code correctly, something triggers proceed23, as a result of which it gathers som data into an array, then runs through the array populating and calling UserForm4 on each pass. Something else, presumably a command button on UserForm4 triggers the calling of Merge2, which unloads UserForm4 and executes the merge. What you don't seem to have is anything to stop proceed23 running through its loop for populating and calling UserForm4. What you probably need is some code in that loop that tells proceed23 to wait until the command button on UserForm4 is clicked before running its next iteration of the loop. Otherwise (without testing) I think what might happen is that proceed23 exhausts its loop before you've even read UserForm4 the first time through.

JennEx · #49 04-16-2013, 05:11 AM

Paul ... hi.

I have to plead ignorance.

1. My efforts to print are failing with the run-time error. Are you suggesting the print can be done by alternative method? Just not with the method I've coded? (wdSendToPrinter)

I think Problem 2 wasn't really a problem. Userform4, to continue through the print cue loop, was active ... it was just behind the maximized Word window. Can you suggest code to ensure that Word generates its merged documents in a minimized state to the taskbar (I assume this would go in the 'merge2' macro?)? Then, somewhere in 'proceed23' after the loop has been exhausted, I will need to add some sort of code to maximize Word for editting. Of course, Excel still resides underneath and can be easily accessed once Word i s closed.

Jenn

macropod · #50 04-22-2013, 05:26 AM

Hi JennEx,

With your Merge2 sub, change:

Code:

If Worksheets("varhold").Range("V1") = "Edit" Then
  .Destination = wdSendtToNewDocument
Else
  .Destination = wdSendToPrinter
End If

to:
.Destination = wdSendtToNewDocument
and change:

Code:

myPath = "u:\Sports13\Workorders\" & Format(Worksheets("varhold").Range("A1"), "ddd dd-mmm-yy")
    If Len(Dir(myPath, vbDirectory)) = 0 Then MkDir myPath
    oDoc2.SaveAs myPath & "\" & (Worksheets("varhold").Range("A46").Value & "docx")

to:

Code:

    With oDoc2
      If Worksheets("varhold").Range("V1") = "Edit" Then
        myPath = "u:\Sports13\Workorders\" & Format(Worksheets("varhold").Range("A1"), "ddd dd-mmm-yy")
        If Len(Dir(myPath, vbDirectory)) = 0 Then MkDir myPath
      .SaveAs myPath & "\" & (Worksheets("varhold").Range("A46").Value & "docx")
      Else
        .PrintOut
      End If
      .Close False
    End With

Regarding the looping issue, I think I'd need to see the code that calls the proceed23 and Merge2 subs before I could address it.

JennEx · #51 04-22-2013, 12:47 PM

Hi Paul!

The code works wonderfully! Thank you so much for preparing this for me.

I'm wondering though, when the user chooses edit, the report is prepared and saved. Is there anyway to keep the report open in Word for the user to work with it once it's been prepared?

Also, I am getting a margin warning with the printing. It's all OK, but how can I disable the warning? It interferes with the automation.

Jenn

macropod · #52 04-22-2013, 03:39 PM

Quote:

Originally Posted by JennEx

I'm wondering though, when the user chooses edit, the report is prepared and saved. Is there anyway to keep the report open in Word for the user to work with it once it's been prepared?

Hi Jenn,
Swap:

Code:

      End If
      .Close False

[/quote]
Also, I am getting a margin warning with the printing. It's all OK, but how can I disable the warning? It interferes with the automation.[/quote]
You could try changing:
.PrintOut
to:

Code:

Application.DisplayAlerts = wdAlertsNone
.PrintOut
Application.DisplayAlerts = wdAlertsAll

JennEx · #53 04-22-2013, 04:35 PM

Swap ....

Code:

        End With
        .Close False

With ... ??

And I figured DisplayAlerts was the way to go ... but since things are working so well right now, I was afraid if I guessed I'd scew it all up.

macropod · #54 04-22-2013, 04:38 PM

Quote:

Originally Posted by JennEx

Swap ....

Code:

        End With
        .Close False

With ... ??

>>>

Code:

           .Close False
        End With

JennEx · #55 04-28-2013, 04:48 PM

Hello Paul ...

I just want to let you know that the printing options are working wonderfully. Thank you so much!!!

However, I've suddenly encountered problems again with the time fields on my reports. They had worked well with mergefield formatting you had suggested in post #2. But, with a field name change in the datasource, came a mailmerge field name change in the reports. The field change had no relation with any times. But after I made the changes to the mailmerge fields, ALL times went back to "12:00 AM" format. It's so weird that all the times went crazy after they were working.

In the body of the document:

Time Start =

{QUOTE{SET ss{=ROUND({MERGEFIELD start}*86400,0)}}{SET hr{=INT(ss/3600) \# 0}}{SET mn{=INT((ss-hr*3600)/60) \# 0}}{QUOTE"{=IF(hr=0,12,hr)}:{mn}" \@ "h:mm "}{IF{=hr}< 12 AM PM}}

Time End =
{QUOTE{SET ss{=ROUND({MERGEFIELD end}*86400,0)}}{SET hr{=INT(ss/3600) \# 0}}{SET mn{=INT((ss-hr*3600)/60) \# 0}}{QUOTE"{=IF(hr=0,12,hr)}:{mn}" \@ "h:mm "}{IF{=hr}< 12 AM PM}}

In the footer of my document:

a) Time 1 =

{ REF ASStart \*charformat }

where ASStart =

{Set ASStart { IF {MERGEFIELD ALtStaffStart} ="X" "2" {QUOTE {SET ss{=ROUND({MERGEFIELD ALtStaffStart}*86400,0)}}{SET hr{=INT(ss/3600) \# 0}}{SET mn{=INT((ss-hr*3600)/60) \# 0}}{QUOTE"{=IF(hr=0,12,hr)}:{mn}" \@ "h:mm "}{IF{=hr}< 12 AM PM}}}}

b) Time 2 =

{ REF ASEnd \* charformat }

where ASEnd =

{Set ASEnd { IF {MERGEFIELD ALtStaffEnd}="X" "2" {QUOTE {SET ss{=ROUND({MERGEFIELD ALtStaffEnd}*86400,0)}}{SET hr{=INT(ss/3600) \# 0}}{SET mn{=INT((ss-hr*3600)/60) \# 0}}{QUOTE"{=IF(hr=0,12,hr)}:{mn}" \@ "h:mm "}{IF{=hr}< 12 AM PM}}}}

The data in the datasource are time formatted values.

macropod · #56 04-28-2013, 05:05 PM

In that case, you can skip all the start & end time calculations and reduce their fields in the body of the document to:
{SET ss {MERGEFIELD start}}
{SET ss {MERGEFIELD end}}
However, it's risky using the same bookmark names this way, so I'd suggest:
{SET ts {MERGEFIELD start}}
{SET te {MERGEFIELD end}}
and updating the REF fields accordingly.

Similarly, the ASStart & ASEnd and fields become:
{Set ASStart {IF{MERGEFIELD ALtStaffStart}= "X" "2" {MERGEFIELD ALtStaffStart}}
{Set ASEnd {IF{MERGEFIELD ALtStaffEnd}= "X" "2" {MERGEFIELD ALtStaffEnd}}
The only complication might be that the 'X' will now evaluate to '12:00AM'. In that case, replace "X" with "12:00AM".

JennEx · #57 04-28-2013, 06:40 PM

Yeah!!! Awesome!
Thanks again Paul ....

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Switches	ilovemypuppy	Word	2	02-08-2012 04:28 PM
Index switches	cksm4	Word VBA	2	02-03-2011 07:21 AM
Mail merge switches	Jan Collier	Mail Merge	0	08-29-2010 06:06 AM
Command Line Switches	Ringmaster	Office	0	08-10-2010 06:27 AM
Help with switches: Word mail merge with Excel	ks_	Mail Merge	0	12-09-2009 05:09 PM