[RESOLVED] Reading & updating an Excel workbook

I need to connect to an Excel workbook to read its content & then update specific cells. I'd like to query it just like a database to get the data I need & then update certain cells with new data. I found some code samples online but I couldn't get any of them to work. Can anyone post a quick code snippet that will allow me to do what I described? The excel file is .xlsx format if that makes any difference. Thanks for any help...

Please post your code and an example of the Excel file you're trying to connect to.
What do you mean by "couldn't get them to work"? Was there an error and if so, what was the error and on which line does it happen? Is it just that no data is returned, or the data returned is not what you expected? What did you expect as compared to what (if anything) was returned.

I have figured out the first part. I can connect to a spreadsheet & pull data into a datatable. I am stuck on updating a specific cell using a 'where' condition.
Here is the code I am using for testing:

Code:

---

        Dim connectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""", excelFilePath)

        Dim query As String = "UPDATE [Sheet1$] SET F9 = 'F009901' WHERE F3 = 11730"

        Using connection As New OleDbConnection(connectionString), command As New OleDbCommand("", connection)
            connection.Open()

            command.CommandText = query
            command.ExecuteNonQuery()

        End Using

---

I am trying to update a cell in column "I"(F9) with "F009901" where the value in column "C"(F3) = 11730. I am using the default column names F3 & F9 because I dont have any column headers.

When I run the code I get a "Data type mismatch in criteria expression." error. Can anyone see what I'm doing wrong?

the mismatch is because...

Code:

---

'F009901'

---

in quote is Text
and

Code:

---

11730

---

is a Integer/Number

I have encountered weirdness when "IMEX=1" is included in the connection string. Try removing that.

Quote:

---

Originally Posted by ChrisE

the mismatch is because...

Code:

---

'F009901'

---

in quote is Text
and

Code:

---

11730

---

is a Integer/Number

---

They are 2 different columns...

Change your Extended Properties to

Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"""

and try again

Changing the IMEX solved the issue, as jdelano suggested. Thanks...