Access Export of data to Excel using VBA - data looks awful

  • Thread starter Thread starter JudyU
  • Start date Start date
J

JudyU

I am exporting data from Access to Excel using VBA with code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryxxxxx",
myFile1, -1

1. First problem is that my data is in currency format in Access - but it
appears as just decimal numbers in exported Excel spreadsheet.

2. Even worse, some of my values in the exported Excel spreadsheet are
strange tiny numbers. For example, there are $0.00 values that appear in the
exported Excel spreadsheet as -7.27595761418343E-12, 5.82076609134674E-11,
etc.... - I really need to get rid of these (these are currency values), they
look awful.

Judy
 
Judy,
I can't reproduce your problem here.
Here is what I tried.
Create a new table - 2 fields
LName - text, Amount - currency.

Created qryExportCurrency based on the table - nothing special.

Used this code to export to a new workbook:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExportCurrency", _
"C:\Documents and Settings\jeanette\CurrencyExport.xls"

The spreadsheet looked like this
LName Amount
Jack $2.95
Mayr $34.15
Zeer $0.00

Access 2003, Excel 2003

Jeanette Cunningham
 
Jeannette,

I'm still having issues. I'm using Access 2002 and Excel 2002. I am
exporting from Access to Excel the results of a query on several tables. The
fields that end up in Excel with the miniscule amounts are computations
(add/subtract) done on the currency fields.

I am exporting to an established Excel workbook - I need to overlay the data
whenever I do the export. Even if I change the cells/columns in the Excel
workbook to currency it does not help, as they get re-establsihed on the
export to my undesirable format/miniscule values.

Thanks

Judy

The
 
Judy,
with the miniscule amounts - do you want them to show up as $0.00 in excel?
If the answer is yes, create a calculated field or fields for these amounts.
In the calculated field, use the IIf function to set the amount to $0.00 if
it is below a certain limit you choose - for example $0.000023.

for example Expr1: IIf([YourField] <=.00023, 0.00, [YourField])
You may need to tweak the IIf a bit, or use a further format function.

Jeanette Cunningham
 
Jeanette - thanks for the great suggestion on the miniscule amounts - yes I
wanted them to appear in Excel as $0.00.

I ended up not needing it though. By using the Currency convert function
Ccur(....), this also ended up solving the miniscule amounts problem. ( I'll
file the use of IIf away for future use in case I have non-currency numeric
fields with a similar problem.)

Thanks

Judy

Jeanette Cunningham said:
Judy,
with the miniscule amounts - do you want them to show up as $0.00 in excel?
If the answer is yes, create a calculated field or fields for these amounts.
In the calculated field, use the IIf function to set the amount to $0.00 if
it is below a certain limit you choose - for example $0.000023.

for example Expr1: IIf([YourField] <=.00023, 0.00, [YourField])
You may need to tweak the IIf a bit, or use a further format function.

Jeanette Cunningham


JudyU said:
Jeannette,

I'm still having issues. I'm using Access 2002 and Excel 2002. I am
exporting from Access to Excel the results of a query on several tables.
The
fields that end up in Excel with the miniscule amounts are computations
(add/subtract) done on the currency fields.

I am exporting to an established Excel workbook - I need to overlay the
data
whenever I do the export. Even if I change the cells/columns in the Excel
workbook to currency it does not help, as they get re-establsihed on the
export to my undesirable format/miniscule values.

Thanks

Judy

The
Click to expand...
Click to expand...
 
Back
Top