Runtime error at invalid index

[wkernan]

Hello all,

Posting to get help with an unspecified error. The objective of the code is to find the value in the last cell in a column (in excel spreadsheet) if a data path is specified or if not initialize to zero. Pretty simple idea that fits into one case structure. However, what I've been able to find is that the code to locate the value in the last cell is a bit more complicated than I had thought. I found an example on the forums and modified it slightly (link below) to meet my needs. After modifying though I get a runtime error "Error -2147352565 occurred at Invalid index." I know the number is for an unspecified error which does little to help me solve the problem and I'm not entirely sure how to use the invalid index tip to help me either. I included links to the code below. Thank you for any help you can give.

GetExcelColumnData.vi = example

ProblemExcelColumn.vi = my code

[Yupeng_Jia]

Hi wkernan, it seems like if your workbook doesn't have a worksheet named Sheet1 you will get this error. Open the Excel file you are trying to open with MS Excel and find out what the correct worksheet name is for that workbook.

[wkernan]

I went back and looked at the set name and have tried both changing it to sheet one and changing the sheet name in labview to match the sheet name in excel. Both still result in the same error. After reading up a lot more on ActiveX (first time using it) I'm starting to think that the maybe something isn't being closed correctly. I've noticed while testing the code that excel continues to run in the background despite being closed and must be ended with the task manager. Also just for clarification the error is listed as occurring at the last close reference vi.

[wkernan]

I went back and looked at the set name and have tried both changing it to sheet one and changing the sheet name in labview to match the sheet name in excel. Both still result in the same error. After reading up a lot more on ActiveX (first time using it) I'm starting to think that the maybe something isn't being closed correctly. I've noticed while testing the code that excel continues to run in the background despite being closed and must be ended with the task manager. Also just for clarification the error is listed as occurring at the last close reference vi.

[MoReese]

Do you know exactly where the error is occurring in the BD?

[wkernan]

Yes in the sense that labview takes me to the sight if the error. It highlights the last "close reference" node right after the invoke node that closes the workbook.

I'm not sure if this is of any significance but if you delete the close reference node that is causing the error the same error occurs but labview indicates the the invoke node to close the workbook is the reason. The error seems to follow this patter of moving back through the program always occurring at the last item to execute.

[MoReese]

Try disabling the Close Workbook Invoke node right before the Close ref.

[wkernan]

By disabling I assume you mean to delete it and I have tried that. And the error simply moves back to the close reference located just before the close workbook invoke node

[wkernan]

Update:

I've managed to get rid of the invalid index error by deleting the last 3 activeX commands. (2 close references, and the invoke node close workbook.) While this fixes the invalid reference error it now creates the problem of not closing excel. Which is expected since I deleted the close references and close workbook. Still looking for any help I can get with this, Thank You

[RavensFan]

Where did you get the example code from?  Does that example work for you?  Who is Chris Fazekas?  I'm not sure that it is even a good example to use.

One of the principles I learned about using ActiveX is that you close any references in the reverse order they are obtained.  In other words, don't close references to higher level objects until you've close the references to the lower level objects within it.  That example and your code is closing the reference to the application almost right away which is probably invalidating the reference to the workbook that was just acquired.

I recommend you look at the links in the Excel Board.  Don't post questions in that board.  It is just a repository of links to other threads that had interesting conversations about Excel.