Text To Number Conversion

[graviz]

I have some vba that dump an access table into a recordset and then into an excel spreadsheet. When the data is exported Columns I:Q (which are numbers) appear as text (with the green triangle in the upper left corner). I have a pivot table that refreshed however it can't do anything with text. I've tried just to change the cells format and it doesn't work. The only way I've been able to change it to a number is to click on the green triangle and select "Convert to Number" How can I do this via VBA? The # of rows will change but the columns will always stay the same. Any ideas?

 

[The_Doc_Man]

My first thought is that at least some nulls appear, or something overflowed a field, such that you have a non-numeric content in those columns that are problematic. Further, it probably appears in the first several rows. I don't remember how many get evaluated, but when Excel does an "automatic format" like you are describing, it does so by looking at the first X rows to decide whether text or numeric formats are going to work.

 

[graviz]

My first thought is that at least some nulls appear, or something overflowed a field, such that you have a non-numeric content in those columns that are problematic. Further, it probably appears in the first several rows. I don't remember how many get evaluated, but when Excel does an "automatic format" like you are describing, it does so by looking at the first X rows to decide whether text or numeric formats are going to work.

Yes there will be some nulls contained in the data, which is why it needs to be converted after the fact. Any ideas?