#46
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#47
|
|||
|
|||
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 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 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] |
#48
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#49
|
|||
|
|||
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 |
#50
|
||||
|
||||
Hi JennEx,
With your Merge2 sub, change: Code:
If Worksheets("varhold").Range("V1") = "Edit" Then .Destination = wdSendtToNewDocument Else .Destination = wdSendToPrinter End If .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") 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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#51
|
|||
|
|||
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 |
#52
|
||||
|
||||
Quote:
Swap: Code:
End If .Close False 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 [Fmr MS MVP - Word] |
#53
|
|||
|
|||
Swap ....
Code:
End With .Close False 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. |
#54
|
||||
|
||||
>>>
Code:
.Close False End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#55
|
|||
|
|||
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. |
#56
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#57
|
|||
|
|||
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 |