rev_s rev_s - 2 months ago 9 Question Object(,) Array index won't find (0,0) even though it's an 11x37 array

first time questioner here. Thanks in advance for any help you can give.

I'm trying to read a bunch of data from a spreadsheet, chop it up, then throw it into a database. I would rather not do things this way, but it's a basic reality of dealing with accountant-types. Thankfully these spreadsheet reports are very consistent. Anyway, I'm using LINQ for SQL to handle the object-to-reference stuff and I'm using Microsoft.Office.Interop to get my Excel on.

I read through a directory full of .xls and for each one, I'm opening the file, getting a couple of specific data from some specific cells, and then getting a range of cells to pick out values.

Private Sub ProcessAFile(ByVal f As FileInfo)
thisFile = openApp.Workbooks.Open(f.fullName)
thisMonth = Split(thisChart.Range("D6").Value, "-").Last.Trim
thisFY = thisChart.Range("L7").Value
thisWorkArea = thisChart.Range("A14", "L51").Value2

thisWorkArea was Dimmed as a global:

Dim thisWorkArea As Object(,)

I'm getting both strings and ints in my range between A14 and L51, so making it an Object array makes sense here. I don't want to go through each row and pick out ranges in Excel, I want to just read it once and then close it.

So I'm getting the following exception:

System.IndexOutOfRangeException was unhandled
Message=Index was outside the bounds of the array.

in this function:

Private Sub fillCurrMonth()
Dim theseRows As Integer() = {0, 2, 3, 5}

For Each i In theseRows
Dim thisMonth As New Month
'make sure category is in Database

thisMonth.Dept = thisDeptName
thisMonth.FY = thisFY
thisMonth.Category = thisWorkArea(i, 0)

"Month" above refers to a LINQ entity. It's nothing fancy.

That last line there is where I'm catching the exception. In my watch, I find that thisWorkArea has a length of 456 and (0,0) -> "Inpatient"{String}

So why am I getting this exception? I put this in your expert hands. I'm still rather new to, so maybe I'm just missing something fundamental.


Excel uses 1-based indicies. This stems from it using VB as it's in-app programming language which traditionally used 1-based indicies.

You'll find Excel returned an array defined as thisWorkArea(1 To 11, 1 To 37) As Object