Function doesn't work when stepping into?

[Doodoo]

I'm working on a function that reads an excel spreadsheet into a multidimensional array, but when I go to debug the program in VS2005 it runs fine, but when I was trying to debug some other stuff and stepping through the program, the function doesn't work. When the program gets to Dim RouteArray As Object(,) = excelSheet.UsedRange.Value, it errors out with HRESULT: 0x800401A8. Doesn't look like it matters what is in the worksheet either...i created a new worksheet with just a few numbers and still get the same error. I haven't programmed since college, so any help is appreciated.

Public Function getWriteSchedule(ByVal sheet As String) As Object
        Dim provider As Globalization.CultureInfo = Globalization.CultureInfo.InvariantCulture

        Dim excel = New Microsoft.Office.Interop.Excel.Application()
        excel = CreateObject("Excel.Application")
        excel.Workbooks.Open(SchedulePathTextBox.Text)
        Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
        excelSheet = excel.Workbooks(1).Worksheets(sheet) '

        Dim RouteArray As Object(,) = excelSheet.UsedRange.Value

        excel.Workbooks.Close()
        excel.quit()
        excel = Nothing
        excelSheet = Nothing
        Return RouteArray
    End Function

 

[DaveSimmons]

Are you sure these both succeeded with the given values for sheet, SchedulePathTextBox.Text?

> excel.Workbooks.Open(SchedulePathTextBox.Text)

> excelSheet = excel.Workbooks(1).Worksheets(sheet)

 

[Doodoo]

As far as i know it works. When i run the program, it seems to run fine. I'm just adding a few things to the program and wanted to step through a few if statements and saw that it bombed out on that function. The text box is tied to a button with a file dialog. The function gets called with the sheet name within the worksheet. The program will use the same spreadsheet each time.

 

[DaveSimmons]

I haven't used VB in many years, but when you say this:

> As far as i know it works.

Did you actually look at the values of the sheet and textbox while stepping, and the results of the Open and = steps?

You might find the incoming values aren't what you expected (when stepping), or that one of the two statements returned an error result.

 

[Doodoo]

The textbox and sheet values look correct when I'm stepping through. I've added some test output when actually running the program and it correctly reads in the spreadsheet. I can't seem to figure out why it bombs out when stepping through though. Its the same spreadsheet and same program....

 

[sm625]

The textbox and sheet values look correct when I'm stepping through. I've added some test output when actually running the program and it correctly reads in the spreadsheet. I can't seem to figure out why it bombs out when stepping through though. Its the same spreadsheet and same program....

It may be the same program but who knows how many DLLs could be debug DLLs rather than release DLLs. When that happens to me I always consider the option of running release code and using messageboxes to help me debug. If what I need to do is simple enough then this is usually faster than tryign to figure out what is going on with the stupid debugger.

 

[DaveSimmons]

The textbox and sheet values look correct when I'm stepping through. I've added some test output when actually running the program and it correctly reads in the spreadsheet. I can't seem to figure out why it bombs out when stepping through though. Its the same spreadsheet and same program....

Did you look at the values of the open function and = assignment before the UseRange command to make sure they succeeded?

 

[Tweak155]

Two things -

excelSheet needs to be checked to make sure its not nothing

and

excelSheet.UsedRange needs to checked to return a valid range.

Also if you have error handling above the function, the error you're seeing may be misleading.