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" <(E-Mail Removed)> wrote in message
news:d9244294-2fda-4c86-ad35-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:d9244294-2fda-4c86-ad35-(E-Mail Removed)...
>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.