Microsoft Office Forums Problems With MergeField Switches

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #46  
Old 04-10-2013, 05:39 AM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #47  
Old 04-15-2013, 06:21 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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]
Reply With Quote
  #48  
Old 04-15-2013, 07:41 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #49  
Old 04-16-2013, 05:11 AM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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
Reply With Quote
  #50  
Old 04-22-2013, 05:26 AM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #51  
Old 04-22-2013, 12:47 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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
Reply With Quote
  #52  
Old 04-22-2013, 03:39 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by JennEx View Post
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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #53  
Old 04-22-2013, 04:35 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #54  
Old 04-22-2013, 04:38 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by JennEx View Post
Swap ....

Code:
        End With
        .Close False
With ... ??
>>>
Code:
           .Close False
        End With
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #55  
Old 04-28-2013, 04:48 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Angry Time Fields back to 12:00A

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.
Reply With Quote
  #56  
Old 04-28-2013, 05:05 PM
macropod's Avatar
macropod macropod is offline Problems With MergeField Switches Windows 7 64bit Problems With MergeField Switches Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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".
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #57  
Old 04-28-2013, 06:40 PM
JennEx JennEx is offline Problems With MergeField Switches Windows XP Problems With MergeField Switches Office 2003
Competent Performer
Problems With MergeField Switches
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

Yeah!!! Awesome!
Thanks again Paul ....
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Switches ilovemypuppy Word 2 02-08-2012 04:28 PM
Problems With MergeField Switches 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


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


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