I am having trouble getting Excel to reliably keep data type settings.

I have an application that writes to Excel and later reads back certain fields into VFP 5.0. The numberformat settings seem to not always stick.

Sometimes if the user goes to a cell and retypes the value that is already visibly there and I had specifically set to a numberformat then I reread it from excel it returns to me the right data type.

Is there a known bug relevant to this and a known reliable method to always force Excel to keep data types?

Commonly I have a problem of reading a value from Excel and getting a type N on something like a serial number. I will try to reformat it to C with "@" but it will not take unless a user hand types what is already there.

The code below sometimes works and sometimes doesn't.

Thanks for all your help.

Larry Nobs



oExcel=createobject('Excel.Application')
oexcel.visible = .t.
oexcel.Workbooks.add
test1 = oexcel.cells(10,10)
? type('test1') && RETURNED O which is what I expected
oexcel.cells(10,10).numberformat = "@"
oexcel.cells(10,10).value='test1'
? type(oexcel.cells(10,10).value) &&RETURNED U I expected a C - returned an O once in this sequence.

test1 = oexcel.cells(10,10).value
? type('test1') &&RETURNED C

? iif(type(test1)='C','CHARACTER','SOMETHING ELSE') &&RETURNED CHARACTER, but sometimes returns SOMETHING ELSE

? type('test1') &&RETURNED C
? type(oexcel.cells(10,10).value) && RETURNED C, But a minute ago it returned U