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?
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
[RESOLVED] Reading & updating an Excel workbook
Reply With Quote
Originally Posted by ChrisE
