identify green triangle in cell programatically?

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

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

--
Regards,
Tom Ogilvy

"Ron" <[email protected]> wrote in message
news:[email protected]...
> 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

Hello Ron,

You can find out which cells have comments easily, since Comments is a collection. The code below will list all cell addresses that contain comments. It will list them on the active sheet starting at cell A1.

For I = 1 To Comments.Count
Activesheet.Cells(I, 1).Value = Comments(I).Parent.Address
Next I

Hope this helps,
Leith Ross

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?


>-----Original Message-----
>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
>
>--
>Regards,
>Tom Ogilvy
>
>"Ron" <[email protected]> wrote in
message
>news:[email protected]...
>> 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
>
>
>.
>

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

--
Regards,
Tom Ogilvy




"Ron" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
> >-----Original Message-----
> >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
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >"Ron" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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
> >
> >
> >.
> >

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


>-----Original Message-----
>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
>
>--
>Regards,
>Tom Ogilvy
>
>
>
>
>"Ron" <[email protected]> wrote in
message
>news:[email protected]...
>> 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?
>>
>>
>> >-----Original Message-----
>> >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
>> >
>> >--
>> >Regards,
>> >Tom Ogilvy
>> >
>> >"Ron" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> 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
>> >
>> >
>> >.
>> >
>
>
>.
>

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





"Ron" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> >-----Original Message-----
> >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
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >
> >
> >
> >"Ron" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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?
> >>
> >>
> >> >-----Original Message-----
> >> >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
> >> >
> >> >--
> >> >Regards,
> >> >Tom Ogilvy
> >> >
> >> >"Ron" <[email protected]> wrote in
> >> message
> >> >news:[email protected]...
> >> >> 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
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >