I have a spreadsheet that has VBA that opens, reads, and closes a list
of other spreadsheets and tabs, writing the contents of those
spreadsheets out as CSV files.

The problem that I am encountering is where one of the sheets has a
column that is not wide enough for all the data, so 1234.5678 is read
as 1234.568. I have been getting around this by doing a
Sheet.Columns.AutoFit call when opening each sheet.

This is now breaking on one particular sheet. When I open it, it shows
1234.5678, but when I manually auto-fit the column, it shrinks a
little bit and changes to 1234.568, and this is the same as is
happening in my VBA code.

Any suggestions as to what I can do in my VBA to read the sheet
accurately? I don't want to use the Value2 property, as this breaks
date cells. The only thing I can think of is to do the AutoFit and
then loop through all the columns increasing the widths.

Phil Hibbs.

Try returning the Formula property:

msgbox activecell.formula

returned 1234.5678 when here was an equal sign.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel



"Phil Hibbs" wrote in message
...
I have a spreadsheet that has VBA that opens, reads, and closes a list
of other spreadsheets and tabs, writing the contents of those
spreadsheets out as CSV files.

The problem that I am encountering is where one of the sheets has a
column that is not wide enough for all the data, so 1234.5678 is read
as 1234.568. I have been getting around this by doing a
Sheet.Columns.AutoFit call when opening each sheet.

This is now breaking on one particular sheet. When I open it, it shows
1234.5678, but when I manually auto-fit the column, it shrinks a
little bit and changes to 1234.568, and this is the same as is
happening in my VBA code.

Any suggestions as to what I can do in my VBA to read the sheet
accurately? I don't want to use the Value2 property, as this breaks
date cells. The only thing I can think of is to do the AutoFit and
then loop through all the columns increasing the widths.

Phil Hibbs.

I'm not sure how you are saving your data out (you didn't show us your
code); however, the Value property of a cell should return the full value in
the cell, not the truncated/rounded value shown when the column is too
narrow.

--
Rick (MVP - Excel)


"Phil Hibbs" wrote in message
...
I have a spreadsheet that has VBA that opens, reads, and closes a list
of other spreadsheets and tabs, writing the contents of those
spreadsheets out as CSV files.

The problem that I am encountering is where one of the sheets has a
column that is not wide enough for all the data, so 1234.5678 is read
as 1234.568. I have been getting around this by doing a
Sheet.Columns.AutoFit call when opening each sheet.

This is now breaking on one particular sheet. When I open it, it shows
1234.5678, but when I manually auto-fit the column, it shrinks a
little bit and changes to 1234.568, and this is the same as is
happening in my VBA code.

Any suggestions as to what I can do in my VBA to read the sheet
accurately? I don't want to use the Value2 property, as this breaks
date cells. The only thing I can think of is to do the AutoFit and
then loop through all the columns increasing the widths.

Phil Hibbs.

Robert Flanagan wrote:
Try returning the Formula property:

Hey, that appears to work! I'll have to do some more testing, but
thanks, I think that might have cracked it. I'll post back here again
if I find any problems with it.

Phil Hibbs.

Hey, that appears to work! I'll have to do some more testing, but
thanks, I think that might have cracked it. I'll post back here again
if I find any problems with it.

It turns out there is a very subtle problem with reading the formula -
some of the cells actually contain formulae, and I want the results of
the formula, not the formula itself.

Phil Hibbs.

Rick Rothstein wrote:
I'm not sure how you are saving your data out (you didn't show us your
code); however, the Value property of a cell should return the full value in
the cell, not the truncated/rounded value shown when the column is too
narrow.

I could have sworn that we tried the Value property and found a
problem with that, and so used the Text property. There must be a
reason why we used Text. I'll run some tests and see what I can find.

Phil Hibbs.

I could have sworn that we tried the Value property and found a
problem with that...

Got it. If the cell has a format, e.g. 2 decimal places, we want that
to be applied. Text gives us that, but Value and Formula don't.

Phil Hibbs.