"Analyze with Excel" - AccXP and Acc 2003

[CP]

I recently upgraded from AccessXP to Access 2003. Earlier, I
developed a query that I run pretty often with a number of columns
containing Currency data. Using Access XP, I formatted these columns
as Currency by using the Properties choice for the field/column so the
query results show as Currency. I then choose the "Analyze with
Excel" choice from the Office Links menu. Excel opens and the
resulting spreadsheet shows the columns as Currency (i.e. with a "$"
character) even though the Cell Format was set to Custom = $#,##0.00;
($#,##0.00). Although this wasn't REALLY currency, it was okay
because the cells could be treated as currency and could be summed,
multiplied, etc. And they APPEARED to be Currency (because of the
"$") which is what I needed to show in reports.

However when I run the very same query in Access 2003 the resulting
spreadsheet shows the cells as numbers and the formatting is Custom =
#,##0.00;(#,##0.00), i.e.WITHOUT the "$". Everytime I run this query
and create the resulting spreadsheet, I have to change the formatting
in Excel for each affected column to Currency (and there are a bunch
of them separated by other columns here and there that aren't currency
format so I can't format all of them at once). It isn't difficult to
do this (manually or with a macro) but I'm really curious about what
changed between the two programs to cause this change. I've looked
through Tools|Options in Excel but can't find anything that appears to
be related to this type of behavior. And I'm using the same computer
for both AccXP and Acc2003 so the Regional settings didn't change.

(I also tried to get around this problem by using the FormatCurrency()
and Format() functions as well as combining those functions with the
Ccur() function in Access but those cells became TEXT in the resulting
spreadsheet which helped less than before.)

I'd appreciate it greatly if anyone has any ideas about this change or
can explain why this is happening. And if you can tell me any
alternate ways to retain the Access formatting in an Excel spreadsheet
without having to reformat columns or run a macro, that would be
wonderful.

Thanks. Carol.

 

[Jeanette Cunningham]

Carol,
I just tried this with my Access 2003 running in A2000 file format and Excel
2003 on Win XP.
The query I used had a column with the format set as Currency and the data
in the matching table field was also Currency.

I can confirm that using analyze it with excel gives the amount as numbers
in excel without the $ symbol.

I tried exporting the same query using Transfer Spreadsheet and like magic,
the $ symbol appeared with the amounts in the spreadsheet.
I don't know why, but I can confirm that this is what happens.



Jeanette Cunningham -- Melbourne Victoria Australia

 

[CP]

Thanks, Jeanette -- I'll give the TransferSpreadsheet option a try
but, unfortunately, this is happening in an application that a lot of
other people use that uses the "Analyze with Excel" as a button on a
custom button bar. I'll have to re-engineer that button bar, I guess.

Thanks again.

Carol.

Carol,
I just tried this with my Access 2003 running in A2000 file format and Excel
2003 on Win XP.
The query I used had a column with the format set as Currency and the data
in the matching table field was also Currency.

I can confirm that using analyze it with excel gives the amount as numbers
in excel without the $ symbol.

I tried exporting the same query using Transfer Spreadsheet and like magic,
the $ symbol appeared with the amounts in the spreadsheet.
I don't know why, but I can confirm that this is what happens.

Jeanette Cunningham -- Melbourne Victoria Australia

[snip]