Fields Are Not formatting

[MaxArk]

I have an Excel database that I am using as my source data.
I have columns in this worksheet representing dates and times, and are
formatted accordingly.

However, when these values are used in my merge document, they do not
take on the proper formatting despite my forced document formatting
code.

ie {MERGEFIELD "Date" \@ "dd-MMM-yy" } is showing the result as a
serial number 39959 instead of 26-May-09

{MERGEFIELD ""F13" \@ ”h:mm AM/PM"} is showing 0.64583333333 rather
than 3:30 PM

Can anyone help me with getting the proper formatting of my fields?

Jenn

 

[Peter Jamieson]

Typically this problem is actually to do with the way that Word gets the
data from Excel - see http://tips.pjmsn.me.uk/t0003.htm for an
explanation and some suggestions.

In addition to that, macropod has suggested previously that it would be
possible to reconstruct the date from an Excel serial number using his
"date field calculation" approach, which you can find at

http://www.wopr.com/index.php?showtopic=249902&st=0&p=249902&#entry249902

I advise you to read the introduction. There is, or was, a section titled

IMPORTING DATE AND TIME VALUES FROM EXCEL AND ACCESS

which may provide the basis for what you need.

For the times,

I think

{ QUOTE "{ SET T { MERGEFIELD F13 } }{ =T*24 \#00 }:{
=((T*24)-INT(T*24))*60 \#00 }" \@"h:mm AM/PM" }

will do it, but again, I am sure you will find more reliable and/or
flexible calculations in macropod's document. All the {} need to be the
special "field code braces" that you can insert using ctrl-F9.

Personally I would normally favour the idea of "getting the data source
right" but the current methods for getting data from Excel do not make
that at all straightforward.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[MaxArk]

Augh!! Couldn't be any easier. LOL

I had a similar application (mail merge report) that took similar data
from an Excel database and had no problems. I have no idea what I did
differently then than what I have tried to do now.

I'm at a loss.

Jenn

 

[Peter Jamieson]

The simplest thing is probably to copy/paste your Excel data into a Word
document, then use that as a data source.

If you're doing a one-off, that's fine. If you're trying to create a
solid workplace tool for others to use, Word-Excel connections are a crock.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[MaxArk]

solid workplace tool for others to use, Word-Excel connections are a
crock.

LOL.

I've spent months working on a great Excel based data processing
application. Excel is my tool of choice. Word has a great presentation
feature with it's mail merge feature, something Excel lacks in. I'm
quite disappointed that integrating them has been so difficult.

What I may consider doing is changing all the date and time cells in
the Excel database to simple text once all the calculations are
complete. The database uses these values in calculations, so an
additional step will be needed to copy the contents of the processed
database to a separate worksheet, one with cells being all text. Now
.... how complicated will it be to convert date formatted 21-May-09 to
pure text 21-May-09?

Jenn

 

[Peter Jamieson]

I'm

quite disappointed that integrating them has been so difficult.

Yes, it is disappointing.

I don't know what the best way to get text versions of the dates into
another column would be, or how well that would work if you still have a
column with material that /looks like/ mixed data types.

Not being an Excel expert and finding no obvious worksheet function to
do the job in the Text functions group, I'd probably start by writing an
Excel VBA worksheet formula such as

Function astext(sourcecell As Range) As String
astext = sourcecell.text
End Function

and put

=astext(<the source cell address>)

in each cell in the target column, but I assume there is actually a
better way.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi Peter,

One possibility is to save the Excel ws to a csv file, then use that as the mailmerge data source.

 

[macropod]

Hi Max,

A macro to modify all the selected Excel cells would be:
Sub Val2Txt()
Dim oCel As Range
For Each oCel In Selection
If IsNumeric(oCel.Value) Then oCel.Value = "'" & oCel.Text
Next oCel
End Sub