Hi,
I'm trying to enter data into an excel spreadsheet. Some of the data is Data Format, 1 is an Integer, 2 are Floats. When it gets to Excel it seems to be entered as Text.
I've tried casting the values as there correct types but Excel still seems to get it as a string.
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\Readings.xlsx; Extended Properties='Excel 12.0 Xml;HDR=YES';"
query = "Select * from [Sheet1$A1:D65535]"
Set rs = CreateObject("ADODB.Recordset")
rs.Open query, cn, adOpenStatic, adLockOptimistic
rs.AddNew
rs("TimeStamp").Value = FormatDateTime(Now(),vbGeneralDate)
rs("Field1").Value = cInt(intValueA)
rs("Field2").Value = cDbl(dblValueB)
rs("Field3").Value = cDbl(dblValueC)
In the Excel Spreadsheet the cell format is set to the right format but still I get the 'The data in the cell is either formatted as text or is proceeded by an apostrophe.
Am I missing something obvious?
Thanks,
Peter