VBScript ADODB and Excel 2013

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

 

September 13th, 2015 7:04am

All data in Excel defaults to text unless you define the column as other.  Adding records with ADO cannot not change the cell/column type.
Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 10:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics