identify green triangle in cell programatically?

[Ron]

Hello,

I am writing data to Excel 2002 from Access using ADO.
This is fast, but I keep getting the little green
triangles in each cell containing numeric data (it is the
way ADO writes the data to Excel). So I have to manually
convert each cell to number from the green triangle. I
tried using RecordMacro to see what was happening, but
RecordMacro is not recording the conversion. I am
guessing that the manual action is just formatting the
cell to numeric and re-writing the value in the cell. I
want to be able to programatically identify cells with the
little green triangle so that I can do the same thing.
Any suggestions appreciated how to programatically
identify the little green triangle in the cell.

Note: I tried formatting the cells to numeric on the
given workbook and saving the workbook (like a template,
but .xls). This worked, except that the data may be
numeric or chars. If ADO tries to write a char to a
numeric cell, it dies. So I have to leave the cells as
general format.

Thanks,
Ron

 

[Tom Ogilvy]

Dim cell as Range
for each cell in worksheet.usedrange _
.specialCells(xlCellTypeFormulas, xlNumbers)
if isnumeric(cell.Value) then
cell.value = cell.value
end if
Next

 

[Ron]

Thanks for your reply. I tried out this code but kept
getting the error message that no cells were found. I
have a range with 15 columns and 5 rows where all the
cells have the little triangle. All the values in these
cells are numbers, so xlNumbers is probably the correct
arg. I tried most of the dropdown args for the first arg
but just got error for each one, "No cells were found"
or "Unable to get the SpecialCells property of the Range
class".

Is there maybe another arg I could use for xlNumbers?

 

[Tom Ogilvy]

No, xlNumbers isn't the right argument - I got myself turned around and
going backwards - my apologies. xlTextValues is the right argument since
the storage of numbers as text is what is causing the error triangles.

Dim cell as Range
Dim rng as Range
On Error Resume Next
set rng = worksheet.usedrange _
.specialCells(xlCellTypeFormulas, xlTextValues)
On Error goto 0
if not rng is nothing then
for each cell in rng
if isnumeric(cell.Value) then
cell.NumberFormat = "0" ' or whatever is appropriate
' unless you want to keep the format as general or text
' whatever it is now.
cell.value = cell.value
end if
Next

 

[Ron]

Thanks again for getting back to me on this. Well, I gave
it a try. With the On Error Resume Next, the code didn't
die this time, but rng did not get set. It just resumed
out. I even tried setting rng to a specific range
("B20:N24") for example, where I have the little green
thigns. No luck, still. I even created my own triangles
where I format a range of cells as text and place
numbers. Still, rng doesn't get set.

My alternative that I did was to designate one static
range like "A1:N50".
Dim x As Variant
For...
For...
If IsNumeric(rng(i,j)) Then
rng(i,j).NumberFormat = "0"
x = rng(i,j)
rng(i,j) = x
End If

This actually, gets rid of the triangles. But I would
like to be able to use the cool properties like
SpecialCells. May I request if you could try your code on
a specific set of cells with the greenies (without resume
next)? If it works for you, then maybe I need to make a
reference to some library? I am using the default
references of Excel Obj Lib 10.0, VB for Apps, VB for Apps
Ext, ...

Thanks again,
Ron

 

[Tom Ogilvy]

Must have been a real bad day when I posted that. Obviously your cells are
constants.

Dim cell as Range
for each cell in worksheet.usedrange _
.specialCells(xlCellTypeConstants, xlNumbers)
if isnumeric(cell.Value) then
cell.value = cell.value
end if
Next


--
Regards,
Tom Ogilvy