4

I'm trying update a cell within an Excel 2003 spreadsheet, but the problem that I'm having is that Excel adds a single quote to the front of my value. Is there any 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

JNK
  • 63,321
  • 15
  • 122
  • 138
ant
  • 171
  • 2
  • 13
  • 1
    Its the OLEDB driver rather than ADO, See http://stackoverflow.com/questions/755621/oledb-writing-excel-cell-without-leading-apostrophe – Alex K. Oct 26 '11 at 13:15
  • Thanks for the link. Looks like the leading apostrophe is unavoidable :( – ant Oct 26 '11 at 13:56
  • FWIW similar code I have that INSERTS into the sheet does not experience this. – Alex K. Oct 26 '11 at 14:11
  • Do you mind sharing this code? – ant Oct 26 '11 at 14:35
  • `ado.Execute "CREATE TABLE [Sheet1$] (COL1 INT, COL2 VARCHAR)" ado.Execute "INSERT INTO [Sheet1$] VALUES (1, 'CAKE')"` – Alex K. Oct 26 '11 at 14:44
  • I managed to solve this with hints from Alex and onedaywhen. Can't self-answer yet but it's basically to set the Excel cell type beforehand! – ant Oct 26 '11 at 16:04

2 Answers2

2

I would guess that the combination of registry values and existing data is causing the data type of the column to be considered as text. Note that when using the OLE DB provider for ACE/Jet, Excel has but one numeric type, being FLOAT (Double).

To discover the considered data type, try running this:

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
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
2

I've managed to solve this with the help of hints from Alex K and onedaywhen.

The solution is to simply format the Excel cell(s) that 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 this a few days ago and it didn't work...but I guess it must have been late in the day!

ant
  • 171
  • 2
  • 13