Change Excel cell types from label to value

[jimbog]

Hi Everybody,

I get spreadsheets from other people (big ones downloaded from databases). The numbers in some cells look like values, but they are really labels (ie, strings?). The attached sheet has an example. I think it's caused by the database field formatting. Yes, if you have Transition navigation keys on (Tools...Options...Transition), you will see the label justification character in cell B1.

I wrote the macro below to change the format of a number, but now I know the format and cell type are two different things.

Basically I'd like to extend this macro to change all cells in the selection to values (in addition to changing the number format). And I'd like to do it using QM COM interface if possible. Although I guess if Excel/Windows sets this below the app level then I will need an alternative...

One way to do it manually is to copy a "1" and then do a Paste Special...Multiply to the target cells. But eventually I will want to make the macro more sophisticated, so I'd like to use COM. Although that does bring me to another point...does anybody know how to use setclip to put an interger (as recognized by Excel) into the clipboard? I guess that may be the way I end up going.

Something like:

int x=1
x.setclip

...which doesn't work. Yes, I tried all the registred formats by copying a "1" from Excel and running the registered format macro.

Any ideas out there??? Thanks.

---------------------------------------------------------------------------

typelib Excel {00020813-0000-0000-C000-000000000046} 1.2
#opt dispatch 1 ;;call functions through IDispatch::Invoke (may not work otherwise)

Excel.Application app._getactive
Excel.Range c=app.Selection
c.NumberFormat="General"

[Gintaras]

Yes, changing cell format does not work. Also need to reenter cells. Using COM it would be

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range ra=es.ws.Application.Selection
ra.NumberFormat="General"
ARRAY(VARIANT) a
a=ra.Value
ra.Value=a

Using copy/paste
str s
s.getsel
s.setsel
key ...

[jimbog]

Hi Gindi,

Is there a way to make this macro change the entire seleted Excel range to labels?

I tried things like "ra.value2" and "ra.text" but did not have luck. Ran into problems like array type mismatches and read-only property types.

-jimbog

[Gintaras]

I think NumberFormat should work now. Don't know what else could be used.

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range selection=es.ws.Application.Selection
selection.NumberFormat = "@"

[jimbog]

Okay, will give it a shot. Thanks.