number format when returning value from odbc connection

[Guest]

hi there,

hope you can help. i have a querytable.add method with odbc connection and
in a particular column that is returned to excel, some values are numeric,
some are alphanumeric. in each cell property, i see the format as "general"
however, if the value is numeric, excel has a little green triangle on the
top left hand corner of the cell. if i hover over it, i get an option to
"convert to number" and i click on it. the green triangle disappears

the reason why i convert is because subsequent vlookup function does not
work on a cell that has not been converted to number, especially when that is
the first column of the table array.

how do i "convert to number" in VBA? i recorded this action with a macro but
the macro doesnt record anything. when i debug my procedure, it is never the
green triangle until the procedure ends

many thanks,

 

[Guest]

Select the cells you want to fix and run:

Sub fixum()
Set rr = Selection
For Each r In rr
r.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub

 

[Guest]

thank you, i didnt think about simulating a keyboard stroke but i understand
the theory. works perfect if i do it manually.

however, when i stick the code in the macro, it doesnt produce the desired
result. when i debug and investigate further, it seems i cant get the
Application.SendKeys method to work at all. do you have any further ideas?

many thanks in advance,