#1
|
|||
|
|||
Problems With MergeField Switches
I cannot understand why certain fields within my mailmerge document are not displaying the proper formats despite my switches.
My datasource is an Excel worksheet. I have time fields, that are displaying in their decimal equivalents rater than the "h:mm AM/PM" format coded. {MERGEFIELD "Start" \@ "h:mm am/pm"} The data in excel is a time value formatted as "h:mm AM/PM". I have one date field displaying it's serial value rather than the "d -MMM" format coded. {MERGEFIELD "close_date" \@ "d-MMM"} The data in Excel, a number, is 41477, and this is how it's displaying in the mailmerge document. Should be 22-Jul. Cross posted here. |
#2
|
||||
|
||||
Hi Jenn,
Have you tried the time mergefield without the switch? Word ordinarily converts it to the AM/PM format without it. You should only need the switch to eliminate seconds from the output. As for the date, if it's displayed as a serial number in Excel, that's what you'll get in Word. To convert the serial number to a date, you'd require field code like: {QUOTE {SET jd{=2415019+{ MERGEFIELD Date }}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"} Note: The field brace pairs (ie '{ }') for the above example are created in the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks
Thanks macropod for your support.
I'll see what I can do with the time format. I think it would be much simpler to try and format the date at the data source. I'll report back with my success or failure. |
#4
|
|||
|
|||
Hi Paul ...
My times, all of them, are still showing up as decimals, despite the mergefields with or without the \@ "h:mm AM/PM" switch. The datasource is in time format (a number, not text as proven using the {isnumber] function) The dates ... well, I have no idea what's going on. One remains the serial number (41438), and the other is in m/dd/yyy (6/13/2013) , with or without the switch. The date values in the datasource or date, formatted as d-mmm, and are true dates, not text. Its as though none of my switches are being acknowledged. When not using switches, iI insert the merge field by using the Insert Merge Field button on the Word ribbon. |
#5
|
|||
|
|||
Why do some mergefields have quotation marks around their names, where others don't.
For example: { MERGEFIELD "Gadv_Date" \@ "dd-MMM" } = 13-Jun { MERGEFIELD "Ptimec" } = <4:45 PM { MERGEFIELD SubRespStart \@ "h:mm am/pm" } =0.5625 but ... { MERGEFIELD "close_date" \@ "dd-MMM" } = 41438 |
#6
|
||||
|
||||
Hi JennEx,
Can you attach a copy of your mailmerge main document with just the problem mergefields and a copy of the data source with just the problem fields to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hi again,
I would be pleased to attach the mailmerge document (DR-HPL8.docx) and it's supporting database (Sports13B.xlsm worksheet "CONTROL_1"). I hope it helps diagnose the problem. |
#8
|
||||
|
||||
Hi JennEx,
There are no apparent errors with mailmerging the data you've attached to your post. That said, I note that you have columns, such as 'Gtimec' where the times are preceded by '<'. You need to be aware that those cells are not storing time values, but text strings. When you have columns with mixed data types (eg text strings and 'real' times) for a mailmerge, the process Word uses to determine the column's data type for the mailmerge can result in unexpected results. For example, if the column starts of with 'real' times or numbers, Word might determine that the column contains time or numeric data and any strings might evaluate as 0 time or 0 value. Conversely, if you have strings, 'real' times might be returned as numeric strings. With that in mind, the safer approach would be to have the '<' in a separate column to the time, such as you have with 's1' and merge both columns.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
No mixed data in the problem columns....
Thank you Paul for looking deeping into a potential problem. What you have said makes perfect sense, and I have been aware of the problems that can arise out of using mixed data type in a specific column. I was careful to ensure that only numbers were in number columns, and text in text column. Those fields with the "<" or ">" I knew were text. These fields are displaying properly.
It's the 4 columns that are purely numbers, that aren't displaying properly in my application ... columns N, O, DG, DH, DI and DJ. Is that theory still valid considering there are no mixed data types in those columns ...? I've attached some pictures of the mailmerge document during result previwe, and then the final document. Perhaps there is something wrong exclusive to my end. |
#10
|
|||
|
|||
Think I busted it!
"Click to select the Confirm file format conversion on open check box" in Word. |
#11
|
||||
|
||||
Hi JennEx,
That might explain it. When I downloaded your files, I had to re-connect to the data source as the folders differ. That could account for me not seeing the same errors you saw.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Further to your initial explanation Paul. I have a field of Data {cutomer$} that takes a concatenated text format of Name (xxx.xxx.xxxx) where the x's represent a telephone number.
When this field is mosted in the mailmerge document, it only displays the name portion. I'm assuming this might be a mixed data scenario you warned about? What would be a possible workaround? |
#13
|
||||
|
||||
Hi JennEx,
mosted? Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Ahhhh ... a nice and easy solution. The kind I like!
Thanks so much Paul for your help. Jenn |
#15
|
|||
|
|||
All's not 100% yet as I'm finding myself running into some issues around this still.
When I use the mailmerge direct from Word, everything works fine. The file conversions check straightened out the date formatting problem I had. However, the problem persists when I launch the Word mailmerge from VBA in my Excel application. My Excel VBA application allows the user to select which report she wishes to use. The code below does everything it's supposed to do, however, the end result is the dates back into decimal format. The same report run direct from Word, and no problems. Thoughts? Solutions? Code:
Option Explicit Dim objword As Object Dim odoc As Object Dim odoc2 As Object Const wdsendtonewdocument = 0 Const wdsendtoprinter = 0 Dim mypath As String Sub merge() Dim fname As String Set objword = CreateObject("Word.Application") objword.DisplayAlerts = True fname = "u:\Sports13\Reports\FR\v8\" & Worksheets("Front").Range("I14") Debug.Print fname Set odoc = objword.documents.Open(Filename:=fname, ConfirmConversions:=True, ReadOnly:=False, addtorecentfiles:=False) objword.Visible = True odoc.Application.Visible = True odoc.mailmerge.Destination = wdsendtonewdocument odoc.mailmerge.Execute Set odoc2 = odoc.Application.documents("Catalog1") odoc.Close False 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") objword.DisplayAlerts = True AppActivate "Microsoft Excel" Set objword = Nothing Set odoc = Nothing Set odoc2 = Nothing End Sub |
|
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 |