Numbers send to Excel in 0.0% format change to 0.00%

[CLarkou]

My program sends data from MSAccess to Excel. Numbers are formatted
from Access as percentages with one decimal point.
ex. in MSAccess number is 8.5% but when send to Excel number becomes
8.50%.

Is there any reason for this ? Can it be avoided ? Is time consuming
to format them again in Excel.

 

[Nick Hodge]

Excel and Access have a few inconsistencies like this and I don't have a
solid answer. I would check your 'percent' style in Excel under
Format>Style... It may not help but is one idea.

You could set a macro which runs after the data is refreshed which sets all
the formats.

Post back if you want to go that route

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[CLarkou]

I had a macro before for setting formats, but now I need to format
them from Access for some calculations to take place. The problem with
the macro was that the formatting I need is more cell format. I tried
to apply row & column format but sometimes some cells had wrong
format. Formatting cell by cell is very slow.
I tried format>style but is strict, if you enter a number it applies
in any cell that formatting is applied, while in some cells I may need
to have numbers without percentage and without decimals, in some
others I may need decimals etc.

 

[John Thow]

The root of this is that Excel's percentage format defaults to 2 decimal
places. From what Nick says, there seems to be a mixture of number formats
in his columns which makes a 'blanket' format change inappropriate. The only
way I can see of avoiding a lengthy cell-by-cell format is to have each format
in its own column and apply formatting column-by-column.

I had a macro before for setting formats, but now I need to format
them from Access for some calculations to take place. The problem with
the macro was that the formatting I need is more cell format. I tried
to apply row & column format but sometimes some cells had wrong
format. Formatting cell by cell is very slow.
I tried format>style but is strict, if you enter a number it applies
in any cell that formatting is applied, while in some cells I may need
to have numbers without percentage and without decimals, in some
others I may need decimals etc.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[CLarkou]

The problem I have John is that user defines column and row
formatting. Column formattings are applied first and then row
formattings. Cell formatting is correct 98%, but I have a problem with
this crossing formatting. That's why I am formatting the numbers from
Access cell by cell which is faster and then sending them to Excel.