Excel Get ActiveX References.vi and closing references -- grrr

[SteveAtBoeing]

I'm new to ActiveX stuff, but eager to learn! 

The "grrr" in my Subject line is a reference to how I feel about LabVIEW's documentation from time to time.  I'm a dinosaur who came from text-based programming, and did a fair amount of C coding, so sometimes with LabVIEW I'm left with this awful feeling in the pit of my stomach like, "Good grief!  How much memory must LabVIEW be hogging up in the background when I use this vi?" or "What happens to those variables (wires) in that subVI when it completes but doesn't close?  What are their statuses when I come back in the next time?" or "What if I put a lot of elements into that array the first time and then started from element zero the second time and just put in a few?  What has happened with the memroy that was allocated when there were a lot of elements?"

Today I'm stewing about this "Excel Get ActiveX References.vi," and what happens to the "ActiveX references" it generates each time I call the subVI in which "Excel Get ActiveX References.vi" lives.  I think that at least one of the "ActiveX references" it generates when I call it is of the type Excel._Application.  Then there appears to be an Excel._Workbook, and others.  You see, I've used "Excel Easy Report.vi" to put some data into an Excel spreadsheet, and I want to tell Excel to do a "Save" on the open spreadsheet.  I think ActiveX is the (a) right way to do that, so I'm wading into the ActiveX fray...  But this "Excel Get ActiveX References.vi" says in its help file, "Do not close ActiveX references opened with the Excel Get ActiveX References VI. References must remain open until the report is closed. Otherwise the error 3001 will occur."  Well, these Excel workbooks that get created by my VI could well stay open until after my LabVIEW VI terminates!

So (finally), here are some of my quesitons:

1)  When I go through my subVI once, pointing to one workbook, I'll get one set of references "created" or "opened" or whatever you call it when ActiveX references spring into existance.  Now, when I exit the subVI, is it going to automatically try to "close" those ActiveX references?  I don't suppose so, since subVI's stay in memory until the calling VI closes.

2)  Now, I come back into my subVI a second time.  New workbook gets created, so I get new references.  Ok, fine.  Uh oh!  What happened to those old references?  I suppose that if I didn't somehow save them off, I've probably lost the ability to get them back (maybe I'm wrong, but I don't need them back), but is LabVIEW going to "close" those old references (from previous times through the subVI) because I can't get to them anymore?  Won't that cause the dreaded error 3001?  If LabVIEW is not going to "close" them, what in tarnation happens to them (the old C programmer in me creeping back out)??  Now it's some oddball, orphaned reference, floating out there, hogging memory, waiting to make something crash intermittently and be a debugging nightmare?

3)  Now, here's the real scary one.  I think I might dodge the "error 3001" bullet in questions 1) and 2), but now let's say the user closes my LabVIEW application while Excel is still open.  All those workbooks are still open.  Presumably, all those ActiveX references I was not supposed to close are still open.  I really hope that LabVIEW is decent enough to close/erase/delete/blow-away (whatever the right word is) all those ActiveX references which were opened/created by "Excel Get ActiveX References.vi" when my program terminates.  But, oh no!  Won't the error 3001 come along then?  I suppose I can just dump it in the shutdown error handling.

Well, thanks for reading my novel.  I don't know what can be done with LabVIEW documentation to make it more satisfying to folks like me, but perhaps someone can weigh in on all my ActiveX questions here.

Thank you in advance,

Steve Brady

[kbbersch]

You need to close EVERY ActiveX reference you open.  If you don't you'll end up with some Excel processes running even after LabVIEW exits.  You can see them in Task Manager.

I, personally, don't like the LabVIEW Report Generation Tool Kit for working with Excel.  I don't think it's flexible enough.  I have a growing library of VIs that I've written that open, manipulate, and close Excel.  Some references I pass from VI to VI and some I close right after I use them.  It all depends on what I'm doing.  If I want to enter read or write data from/to a certain range I'll get the range reference, read or write the data, then close it right away because I have no use for it any more.  On the other hand, when I open Excel or a Workbook I keep the reference until I'm done, which could be later in the program.

---

1)  When I go through my subVI once, pointing to one workbook, I'll get one set of references "created" or "opened" or whatever you call it when ActiveX references spring into existence.  Now, when I exit the subVI, is it going to automatically try to "close" those ActiveX references?  I don't suppose so, since subVI's stay in memory until the calling VI closes.

2)  Now, I come back into my subVI a second time.  New workbook gets created, so I get new references.  Ok, fine.  Uh oh!  What happened to those old references?  I suppose that if I didn't somehow save them off, I've probably lost the ability to get them back (maybe I'm wrong, but I don't need them back), but is LabVIEW going to "close" those old references (from previous times through the subVI) because I can't get to them anymore?  Won't that cause the dreaded error 3001?  If LabVIEW is not going to "close" them, what in tarnation happens to them (the old C programmer in me creeping back out)??  Now it's some oddball, orphaned reference, floating out there, hogging memory, waiting to make something crash intermittently and be a debugging nightmare?

3)  Now, here's the real scary one.  I think I might dodge the "error 3001" bullet in questions 1) and 2), but now let's say the user closes my LabVIEW application while Excel is still open.  All those workbooks are still open.  Presumably, all those ActiveX references I was not supposed to close are still open.  I really hope that LabVIEW is decent enough to close/erase/delete/blow-away (whatever the right word is) all those ActiveX references which were opened/created by "Excel Get ActiveX References.vi" when my program terminates.  But, oh no!  Won't the error 3001 come along then?  I suppose I can just dump it in the shutdown error handling.

---

1)  No, LabVIEW will NOT close those references.  You need to make sure that happens.

2)  You can save the references in a functional global or use a class but if you're not going to save them close them as soon as you're done with them.

3)  Your user should not be able to close your LabVIEW application without it going through the shutdown routine you've created for your program.  The ABORT button should never be exposed to the user and you should capture and discard the panel close event so your program ALWAYS shuts down is an orderly fashion.  If you don't you will have fragments of Excel hanging around in your operating system and will have to kill those processes using Task Manager.  That should only be a problem during development, not once deployed.

I used to program in C and Assembly many moons ago.  You should have seen my first LabVIEW code.  I go back and look at it just so I can see how far I've come in the last 12 years.  I feel your pain.

[SteveAtBoeing]

Kelly,

Thanks for the info!  I kudo-o-tized your answer because it had lots of good information in it.  But, it still leaves me unanswered in a lot of areas.

You wrote, "1)  No, LabVIEW will NOT close those references.  You need to make sure that happens."  Well, that neglects what NI wrote in their documentation ("Do not close ActiveX references opened with the Excel Get ActiveX References VI. References must remain open until the report is closed. Otherwise the error 3001 will occur."), coupled with the fact that Excel will almost certainly remain running after the user terminates my program.  So these questions remain:

1)  Does LabVIEW, or does it not, automatically get rid of all existing ActiveX references upon VI termination?  I saw that you said I might find orphaned Excel tasks running after VI termination, but isn't that a different issue?  Are ActiveX "references" actually little tasks which run out there?  I suppose that might be the case, huh?  Maybe I could test that by watching task manager while my code runs...  But you see my frustration coming back to NI's documentation!  Why should I have to spend my time writing a test program for something that really ought to be spelled out in the documentation?!  Good grief!  I can't be the only person who has ever thought of such a quesiton or cared about its answer.  Maybe NI's answer would be I should sign up for some training course from them.  Great.

2)  You have told me that I should "close [references] as soon as you're done with them."  Well, yes, that would be my default position.  But, I go back to NI's admonition in the documentation about "Excel Get ActiveX References.vi," namely, "Do not close ActiveX references opened with the Excel Get ActiveX References VI. References must remain open until the report is closed. Otherwise the error 3001 will occur."  Once my subVI has written the data out to the Excel workbook, I'm done with it.  I'd close the ActiveX reference right then and there, but NI tells me not to.  If I do commit the sin of trying to close an ActiveX reference opened with "Excel Get ActiveX References.vi," does the reference successfully close, and I just have to suffer the error 3001, or doe the close operation fail?  You see, I want to leave Excel open so the user can go have a look there and do whatever he wants.

You said, "I, personally, don't like the LabVIEW Report Generation Tool Kit for working with Excel."  But is the VI in question, "Excel Get ActiveX References.vi" part of the LabVIEW Report Generation Tool Kit?  If it is, and therefore you don't use that VI to obtain your ActiveX references to objects such as spreadsheets within a workbook, or workbooks within a file, then how do you get those ActiveX references?  If, on the other hand, you actually do use "Excel Get ActiveX References.vi," then how do you get around the "error 3001" issue?

Ooh!  I just thought of one more question!  That VI "Excel Get ActiveX References.vi" has lots of reference outputs I can wire (the Excel file, a workbook within the file, a spreadsheet within the workbook, etc.)  If I take only one reference out of the function (I'm using the ActiveX reference to the workbook), then do the other references get created or not?

Thank you,

Steve

[Darren · Accepted solution]

Here's the diagram of Excel Get ActiveX References.vi:

No matter how many times you call this VI, the references returned are always the same. So calling this VI in a loop has no effect on memory, performance, etc.

The reason you aren't supposed to close these references is that they are stored internally to your report reference (as part of the class data), and are used by other VIs in the Report Gen API. Those references need to be kept open until you are done calling Report Gen VIs, at which point you should call Dispose Report.vi as the last VI in your "chain". Dispose Report.vi looks like this (for the Excel implementation):

This is where those references get closed. You should always call Dispose Report.vi to make sure all ActiveX references used by this particular report object are closed. If you don't, then there is the potential for them to be kept in memory by LabVIEW, even if the VI goes idle. LabVIEW is able to auto-dispose some reference types when VIs go idle, but I don't believe ActiveX references are one of those types.

Let me know if you still have any questions. As a general rule, the Report Gen VIs should be providing all of the functionality you need for manipulating your report...the Get ActiveX References VIs are only there for cases where you need to do something the Report Gen VIs don't allow you to do.

[SteveAtBoeing]

Thank you Darren!

Something just clicked!  That "Dispose Report.vi" was the missing link for me.  I see in my code that I start off with "New Report.vi," but did not connect that "report" word with the data type (or name or whatever) of the wire that comes out.  Coming into the code pictures you posted are OBJ's called "report in".  Oh!  "report" means something specific here!  You see, when I first read the scary warning in the documentation for "Excel Get ActiveX References.vi" ("Do not close ActiveX references opened with the Excel Get ActiveX References VI. References must remain open until the report is closed. Otherwise the error 3001 will occur."), I just saw "report" in there to mean the Excel workbook that was opened.  But no!  The "report" that has to be closed is the blue, chainy-looking wire.  It's the ActiveX (?) link between my LabVIEW program and the instance of Excel that was opened.  It is not the Excel "report" (instance, file, workbook, whatever) itself.

The reason I'm even bothering with "Excel Get ActiveX References.vi" is so I can send a "Save" command to Excel to save the data I just dumped into it.  I guess I'm actually "Invoking the Save method" of the workbook that got opened.  That seems to work quite nicely, thank you!

Thanks again,

Steve

[Darren]

You mentioned needing the references to save the report. Could you instead just call Save Report to File.vi? You should almost always be able to do everything report-related with the Report Gen VIs without needing to access those internal ActiveX references:

Alternatively, if you have an existing .xlsx file on disk before the code runs, you don't even need the call to Save Report to File.vi... Dispose Report.vi has an input that allows you to save before closing:

One more thing...if all you're doing is dumping data to an Excel file, and you're in LabVIEW 2013 or later, you don't even need the Report Gen VIs. The Write to Measurement File Express VI has the option to save data to an .xlsx file in LabVIEW 2013 and later:

Let me know if you have any other questions.

[SteveAtBoeing]

Huh!  Maybe I should have used "Append Table to Report.vi."  I used "Excel Easy Table.vi"

This is about half awful, since "Excel Easy Table.vi" adds borders and changes column widths, backgrounds, etc.  I then have several calls to undo all that.  Sheesh.

You also wrote and illustrated, "Dispose Report.vi has an input that allows you to save before closing."  I haven't put "Dispose Report.vi" into my code yet, but I hope it won't actually close down the instance of Excel that opened.  I want Excel to keep running.  I just want my code to open Excel and barf the data over there, then forget the event ever happened.  I want the user to see Excel continue to run.  I'll experiment with "Dispose Report.vi," but not tonight.

Thanks again,

Steve

[Darren]

Yeah, I would try Append Table to Report.vi...I don't think it messes with borders or column widths.

Also, you should always use Dispose Report.vi...it closes out all the references opened by LabVIEW throughout the course of generating your report. In general, all reference-based APIs in LabVIEW will have a Dispose/Clear/Destroy/Close/Release VI or function that you call at the end of the "chain". Maybe the "Dispose" in the name is making you uneasy? It's not actually disposing of the .xlsx file...it's just disposing of the report *reference*. And it does have an input that allows you to ensure that Excel is not closed:

[kbbersch]

Part of the reason I don't use the Report Generation Toolkit is that I don't think it's flexible enough and, truthfully, I don't find it that hard to use ActiveX to control Office.  I write my own functions and add them to my growing library of Excel automation functions.  Writing to and reading from Excel is just not all that hard.  Formating cells, inserting charts and grapics, finding data, etc. is slightly harder but only needs to be figured out once and a VI written.  After that it's pretty easy to use the functions.

[SteveAtBoeing]

Darren,

I saw that terminal to which you wired a "False" when I looked at the "Dispose Report.vi".  I figured it meant that Excel would be left running, which is just what I want to happen.  I hope I'll get to implement it today (been pulled off on other projects, but I think I can get back to this one this afternoon!)

Thanks again Darren and kbbersch,

Steve