Hi guys. I'm having problems in updating records in Excel. Whenever
I update a cell the value I input it goes 4 cells above the intended
cell and I know the reason. In my Select query:
SELECT * FROM [Sheet1$A5:R1048576] where [ITEM CODE] is not null
Meaning I skipped reading 4 cells above because my column headers
are at 5th row. The first 4 cells are reserved for other data.
Now I want to update on the intended cell and I can't find any
solutions for it. Also my excel spreadsheet has formula.
'Select Query Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;") Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A5:R1048576] where [ITEM CODE] is not null", MyConnection) MyConnection.Open() adpt = New OleDbDataAdapter(cmd) Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt) DtSet = New DataSet() adpt.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0).DefaultView MyConnection.Close() 'Update Query Dim updt As OleDbCommand = New OleDbCommand("Update [Sheet1$A5:R1048576] Set [Inventory on Hand] = '" & Label4.Text & "' where [ITEM CODE] = '" & Label5.Text & "'", MyConnection) MyConnection.Open() updt.ExecuteNonQuery() MyConnection.Close()