Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number format when returning value from odbc connection
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, -- MS SBS 2003 SP2 -- MS SBS 2003 SP2 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
number format when returning value from odbc connection
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 -- Gary''s Student - gsnu200735 "Mentos" wrote: 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, -- MS SBS 2003 SP2 -- MS SBS 2003 SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
number format when returning value from odbc connection
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, -- MS SBS 2003 SP2 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200735 "Mentos" wrote: 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, -- MS SBS 2003 SP2 -- MS SBS 2003 SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC Connection | Excel Programming | |||
ODBC connection question | Excel Programming | |||
ODBC Connection | Excel Programming | |||
ODBC connection by udl | Excel Programming |