I’m looking to read a range of data from an Excel file into a list so I can process it further. The Excel file will be a standard template, but I want to accommodate more or less rows/columns and not hard code the range to be read. From the picture below I would want A4 through U23 to be read. I’ve started down the path of finding the cell with “User 1” and “Associated Hazards” with the intent to use those to get the range of cells I want to read. It seems like there should be a more efficient method to do this.
My code below returns $A$3 and $U$3, which is a decent start but still leaves a lot work. I’d appreciate examples for a better method of doing this.
Dim OpenDialog As New OpenFileDialog
OpenDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
OpenDialog.Title = "Open Template File"
If (OpenDialog.ShowDialog() <> Windows.Forms.DialogResult.OK) Then Exit Sub
Dim objXLApp As New Excel.Application
objXLApp.Workbooks.Open(OpenDialog.FileName)
Dim objXLWs As Excel.Worksheet = objXLApp.Workbooks(1).Worksheets("Sheet1")
Dim objRange As Excel.Range
objRange = objXLWs.UsedRange
Dim FirstTableStart As String
Dim FirstTableEnd As String
FirstTableStart = objRange.Find("User 1").Address
With objXLWs.Range(objRange.Find("Associated Hazards").Address).MergeArea
FirstTableEnd = .Cells(.Rows.Count, .Columns.Count).address
End With
MsgBox(FirstTableStart & vbNewLine & FirstTableEnd)
objXLApp.Quit()
The cells “User 1” and “Associated Hazards” will not change in value, but the row/column might change and the table will always have the last column full of “#” to the bottom row that needs read. There will be other data to the right and below this table.
