Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 -- MaxArk |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 -- MaxArk |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Fields Are Not formatting
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting of merge fields | Mailmerge | |||
Numeric Formatting: # vs. x (Fields) | Microsoft Word Help | |||
Formatting Ref fields to look like hyperlinks | Microsoft Word Help | |||
formatting if fields | Microsoft Word Help | |||
Formatting of fields | Microsoft Word Help |