Update Excel 2007 with OleDb

Question

Attempting to execute an update command against an Excel 2007 file gives the error: Operation must use an updateable query. I'm using System.Data.OleDb with a connection string like this:

Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & pathToFile & """;" & _
    "Extended Properties=""Excel 12.0;HDR=YES"""

I have tried setting ReadOnly=false but that gives Could not find installable ISAM. I have also tried setting Mode=ReadWrite and IMEX=1 which didn't seem to have any effect. My update command is like this:

 Dim cmd As OleDbCommand = con.CreateCommand()
    cmd.CommandText = "UPDATE [" + sheetName + "] SET [Quantity Error] = 'test' WHERE [Full Name] = 'Mr. Brown White'"

where sheetName was obtained from querying the excel schema. Is it possible to do what I am trying to? Where have I gone wrong?

Answer 1

I believe this is usually a permissions issue. Can you read/write to the file if you open it in Excel?

Answer 2

You usually need a $ sign after the worksheet name. Try:

cmd.CommandText = "UPDATE [" + sheetName + "$] SET [Quantity Error] = 'test' WHERE [Full Name] = 'Mr. Brown White'"

Names without $ signs are interpreted as named ranges and not as worksheet names