Problems With MergeField Switches

JennEx · #1 04-03-2013, 04:59 AM

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.

macropod · #2 04-03-2013, 05:54 AM

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.

JennEx · #3 04-03-2013, 06:02 AM

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.

JennEx · #4 04-03-2013, 04:47 PM

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.

JennEx · #5 04-03-2013, 05:15 PM

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

macropod · #6 04-04-2013, 12:01 AM

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.

JennEx · #7 04-04-2013, 10:22 AM

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.

macropod · #8 04-04-2013, 02:41 PM

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.

JennEx · #9 04-04-2013, 04:00 PM

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.

JennEx · #10 04-04-2013, 04:29 PM

Think I busted it!
"Click to select the Confirm file format conversion on open check box" in Word.

macropod · #11 04-04-2013, 04:37 PM

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.

JennEx · #12 04-04-2013, 05:21 PM

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?

macropod · #13 04-04-2013, 06:18 PM

Hi JennEx,

mosted?

Quote:

I'm assuming this might be a mixed data scenario you warned about?

I don't think that would be the case here, as the field data type for an alpha-numeric string would be 'text' (i.e. you're not dealing with a situation in which different fields have different data types). What I think the issue probably is is that you have both a Customer and a Customer$ field, and your mailmerge is only picking up the Customer field (the $ character can't be used as part of a mailmerge field name). You need to refer to Customer$ field as Customer1.

JennEx · #14 04-05-2013, 03:31 AM

Ahhhh ... a nice and easy solution. The kind I like!
Thanks so much Paul for your help.

Jenn

JennEx · #15 04-05-2013, 09:38 AM

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