Accurately reading the contents of another spreadsheet

[Phil Hibbs]

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]

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

 

[Rick Rothstein]

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.

 

[Phil Hibbs]

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.

 

[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.

 

[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.

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.

 

[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.