Updating Wrong Cell in Excel 2007

[jared.geli]

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()

[khair.ullah]

Try like this

Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")

'Update Query

Dim updt As OleDbCommand = New OleDbCommand("Update [Sheet1$] Set [Inventory on Hand] = '" & Label4.Text & "' where [ITEM CODE] = '" & Label5.Text & "'", MyConnection)
MyConnection.Open()
updt.ExecuteNonQuery()
MyConnection.Close()

'Select Query

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$] 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()

[jared.geli]

That was my first code and it didn't worked. The problem was the first 4 rows are reserved for other data. The first row will be read as header and my column headers from 5th row are being read as data. Either way I'm dropping that idea, I've formatted my Spreadsheet where the column headers are on the first row. Anyway thanks for dropping by.