ADODB query updates an Excel cell as escaped text

Question

I am trying to update a cell in an Excel 2003 spreadsheet, but the problem I encountered is that Excel is adding one quote at the beginning of my value. Is there a way to enter the value as an integer, and not as an escaped string?

Thanks!!

Const filePath = "C:\1.xls" Dim ado: Set ado = CreateObject("ADODB.Connection") ado.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=""" _ & filePath & """;Extended Properties=""Excel 8.0;HDR=No"";" ado.CursorLocation = 3 ado.Open ado.Execute "UPDATE [Sheet1$A1:A1] SET F1 = 1;" ado.Close 

The above code updates cell A1 to '1

Answer 1

I would suggest that a combination of registry values ​​and existing data causes the column data type to be treated as text. Please note that when using the OLE DB provider for ACE / Jet, Excel has only one digital type: FLOAT (Double).

To detect the data type in question, try the following:

 Const filePath = "C:\1.xls" Dim ado: Set ado = CreateObject("ADODB.Connection") ado.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=""" _ & filePath & """;Extended Properties=""Excel 8.0;HDR=No"";" ado.CursorLocation = 3 ado.Open Dim rs Set rs = ado.Execute("SELECT DISTINCT TYPENAME(F1) FROM [Sheet1$A1:A1];") MsgBox rs.GetString ado.Close 

Answer 2

I managed to solve this with tips from Alex K and onedaywhen.

The solution is to simply format the Excel cells (s) I want to update with the correct type (number, custom, etc.) before running my script.

The strange thing is that I'm sure I tried it a few days ago, and it didn’t work ... but I think it must have been at the end of the day!