Fields Are Not formatting

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

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?showto...#entry249 902

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 wrote:
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

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

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 wrote:
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

Originally Posted by Peter Jamieson

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

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

MaxArk wrote:
Peter Jamieson;3341863 Wrote:
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

Hi Peter,

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

--
Cheers
macropod
[Microsoft MVP - Word]


"Peter Jamieson" wrote in message ...
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

MaxArk wrote:
Peter Jamieson;3341863 Wrote:
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

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

--
Cheers
macropod
[Microsoft MVP - Word]


"MaxArk" wrote in message ...

Peter Jamieson;3341863 Wrote:

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




--
MaxArk