Convert to Number

[Tim]

I appologize for the re-post, I should have put this here originally...

I'm filling an excel (2003) worksheet using the 'Excel Files' DSN through
ODBC. I can get the data into the spread sheet no problem, but some of the
data cells have the little green info triangle that says 'The number in this
cell is formatted as text...'. When this happens, the formats I applied to
the column (currency, percent, etc) don't work.

I've made sure there are no extra characters during the input, I've used
Val() and CDbl() during the input, and I've tried a couple other obvious
things but some columns just won't accept the number as a number. Some do
and some don't. Even more odd, if I just go into the 'fx()' text area at
the top and then click an any cell (without typing anything), the number
converts.

How can I get Excel to take my number as a number?


Sample:
sDataInsert = "insert into [data$](f1, f2, f3) values (CDbl(3500.92),
Val(3999.99), 0.03034)"
Call oFileConnection.Execute(sDataInsert )

 

[Ken Wright]

Easiest way to clean it up is to use Dave McRitchies Trimall macro from here:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Any entries remaining as text can be converted to numeric by copying an empty
cell, selecting your range and then doing Edit / Paste Special / Add

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I appologize for the re-post, I should have put this here originally...

I'm filling an excel (2003) worksheet using the 'Excel Files' DSN through
ODBC. I can get the data into the spread sheet no problem, but some of the
data cells have the little green info triangle that says 'The number in this
cell is formatted as text...'. When this happens, the formats I applied to
the column (currency, percent, etc) don't work.

I've made sure there are no extra characters during the input, I've used
Val() and CDbl() during the input, and I've tried a couple other obvious
things but some columns just won't accept the number as a number. Some do
and some don't. Even more odd, if I just go into the 'fx()' text area at
the top and then click an any cell (without typing anything), the number
converts.

How can I get Excel to take my number as a number?


Sample:
sDataInsert = "insert into [data$](f1, f2, f3) values (CDbl(3500.92),
Val(3999.99), 0.03034)"
Call oFileConnection.Execute(sDataInsert )

 

[Tim]

Thanks Ken.

I actually found my problem had to do with merged cells above the point at
which I was inserting data. Imagine I have 4 columns and above them is a
single merged cell acting as a header. The first column was the one with
the convert problem and it turned out it was because of the text in the
merged cell (first real column). Apparently the insert didn't care what I
flagged the column as, it simply saw the text in the merged cell (first real
column) and assumed the column was text.

By removing the merged cell, things worked as I'd expect.

Easiest way to clean it up is to use Dave McRitchies Trimall macro from here:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Any entries remaining as text can be converted to numeric by copying an empty
cell, selecting your range and then doing Edit / Paste Special / Add

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

I appologize for the re-post, I should have put this here originally...

I'm filling an excel (2003) worksheet using the 'Excel Files' DSN through
ODBC. I can get the data into the spread sheet no problem, but some of the
data cells have the little green info triangle that says 'The number in this
cell is formatted as text...'. When this happens, the formats I applied to
the column (currency, percent, etc) don't work.

I've made sure there are no extra characters during the input, I've used
Val() and CDbl() during the input, and I've tried a couple other obvious
things but some columns just won't accept the number as a number. Some do
and some don't. Even more odd, if I just go into the 'fx()' text area at
the top and then click an any cell (without typing anything), the number
converts.

How can I get Excel to take my number as a number?


Sample:
sDataInsert = "insert into [data$](f1, f2, f3) values (CDbl(3500.92),
Val(3999.99), 0.03034)"
Call oFileConnection.Execute(sDataInsert )

 

[Ken Wright]

When you insert Rows or Columns, they will take on the properties of the
Row/Column immediately above/to the left. With text in your merged cell, the
row inserted below will then take on the text property from the cell above.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Thanks Ken.

<snip>