rev_s rev_s - 2 months ago 9
Vb.net Question

vb.net 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
openApp.Workbooks.Close()
...


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 vb.net, so maybe I'm just missing something fundamental.

Answer

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

Comments