Word merge - odd behaviour

[Dollar]

Hi
Got a little problem with a document that I am attempting to merge information to. The source document is an excel spreadsheet, a fairly large one with approximately 400 rows of information. What I am trying to accomplish is a data merge into word so that information relating to a particular client shows in the doc in an "invoice" type of manner.
The first problem was getting the amounts not to drop off 0's which I have resolved using a switch. There is a second and third problem:
1. The date format is mm/dd/yyyy and I need it to be dd/mm/yyyy and use the date from the spreadsheet. I can make the date dd/mm/yyyy but it uses the current date which is not what I want.
2. Loads of data is not merging across. An example: I'd use Amount1, Amount2, Amount3 (and so on). There are quite a number of merge fields that are just not pulling the data and I don't quite know why. The problem seems to be (but is not isolated to) when an excel cell has an alpha-numeric value i.e. 245EUR or 180USD (indicating Euros or US Dollars). I have verified the formats of several cells and everything seems to be in order.

Any help on this would be greatly appreciated.
Thanks

 

[rpochoda]

Two quick thoughts: Word has formatting switches that can be added to the field codes, which should give you your 0's back.
Second, you want all the values in a particular merge field to be either numeric or alpha. In Excel, are you typing the "EUR" and "USD" or have you created custom formats to add them to the display, thereby preserving them as numeric fields?

 

[rpochoda]

Sorry, just noticed you had already solved the 0's problem. For the dates, I'm not sure if Word has Year(), Month(), and Day() functions to parse the parts of a merge date but if not, you can do it in Excel. Parse and rearrange the date in a new field, then send it to Word.

 

[Dollar]

Hi
Thanks for the info.
I'm not entirely sure how to do the parsing thing, but all the dates show in their correct format in Excel, which makes it a bit frustrating.
As for the alpha-numeric values. These are actually pulled in via another datasource. They are not actually manually typed in. I've tried every possible format for the cells, from text to numeric and custom types, but it just won't merge the data.
Any other info greatly appreciated.
Everything displays correctly in excel. It's when the merge dialog comes up that the data appears to change format.

Thanks again.
Dollar

 

[jwa6]

Two quick thoughts: Word has formatting switches that can be added to the field codes, which should give you your 0's back.


-- where are these formatting switches in word?

 

[Dollar]

Was that a question?
In word use "alt + F9" to turn on the merge fields' codes and add as the text below:

{ MERGEFIELD "Open_Amt" \# ",0.00;-,0.00" }

*note the space between first and last brackets/braces

If you were to add the line as:
{ MERGEFIELD "Open_Amt" \# "$,0.00;-$,0.00" }
it would show with $ symbols. The first "0.00" depicts positive values and the second set negative. If you want to show negative values in brackets rather than with a "-" symbol, as below:
{ MERGEFIELD "Open_Amt" \# "$,0.00;($,0.00)" }

Normally, a default merge would show:

{ MERGEFIELD "Open_Amt" }

where Open_Amt is the field name from the source file.

I would still like to know about the date and information not coming across though...

Anyone?

Cheers

 

[Dollar]

Fixed all but the date with Office 2003.

Not concerned about the date anymore though, so it's all singing and dancing

Thanks for all the help - learnt a lot about merge through this.