Have Geek Brain

Update Excel 2007 with OleDb

Attempting to execute an update command for an Excel 2007 file gives an error: The operation must use an updatable query. I am 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've tried setting ReadOnly = false, but that gives Unable to find the ISAM to be installed. I also tried setting Mode = ReadWrite and IMEX = 1 which didn't seem to have any effect. My update command looks 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 excel schema request. Can I do what I am trying to do? Where did I go wrong?

0


a source to share


3 answers


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

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


Unsigned $ names are interpreted as named ranges, not worksheet names

+1


a source


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



0


a source


Use this connection string, it will work

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reportFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\";

In addition to checking the permissions on the Excel file, check if the extended properties of the connection string contains an expression IMEX=1

. If so, remove it.

0


a source






More articles:


All Articles