Dataview not showing updates with AutoFill in Excel

[jcheak]

built an excel add in with a custom save button to gather data from an excel workseet, and call a stored procedure to save this new data.

​

When I try to use the Auto Fill feature in excel, I am able to drag a value(copy) with my mouse to cells underneith the selected cell - this part is fine. I can see the values in the cells below on my excel sheet, all the same, which is what I want. The problem is when I try to see the rows which have values that are changed/updated in a dataview in the vb.net code. The dataview object only seems to have the first cell that was changed. It is as if the other 30+ cells that I copied data into, did not register, even though they are clearly there on the excel worksheet, so my datarowview object only has 1 element and not 30.
​

​

I am trying to use a datatable object to create a dataview. With the dataview, I try to use a foreach loop to iterate through the dataview with datrowview objects. Also, in this column I am validating the data entered through a list. So, when you click on the cell, a validate box is avaiIable for the user to choose only from a list of 10 items. have researched trying to recalculate the range object as well as the worksheet object with the calculate() method, but nothing seems to work. Last, I also considered using the DataSetName.AcceptChanges() method to try to force the data to be updated, but this didnt seem to work for me. Has anyone had this same problem?

​

Here is the relevant code....
​

​

rangeObject.Calculate()

worksheetName.Calculate()

DatasetName.AcceptChanges()

Dim​

​

dv As New DataView(DatasetName.Tables(0))
​

'Dim dv As New DataView(dt)

dv.AllowNew = True

dv.AddNew()

dv.AllowEdit() = True

'dv.RowStateFilter = DataViewRowState.ModifiedCurrent - commented out for experiments

dv.RowStateFilter = DataViewRowState.CurrentRows

​

For Each dvr As DataRowView In dv

''Try to read each cell in each drv to get parameters to send to a stored procedure

Next
​

​

​

Note: The stored procedure works for the first one(since there is data)​

​

​

​