Clean Closing of Excel Application Automation

[Mike_B.]

Through trial and error I have just discovered the importance of closing ActiveX references.

Is it a rule of thumb that all references on a front panel should be closed using the ActiveX automation close function, or do I also need to close the dangling refnum outputs of every method and property node within the diagram?

A little background:
I have a subvi which simply loads an Excel workbook at the click of a front panel button. First execution was fine, but any subsequent execution caused the workspace of the workbook to appear transparent (I could see the Windows desktop through the Excel Application). When I looked at my diagram, I found that I was closing the workbook refnum but not the Excel._Application refere
nce.

So obviously a memory problem affected video performance because I was not closing all references.

[Mark A. Hanning-Lee]

Mike B. wrote:
>
> Through trial and error I have just discovered the importance of
> closing ActiveX references.
>
> Is it a rule of thumb that all references on a front panel should be
> closed using the ActiveX automation close function, or do I also need
> to close the dangling refnum outputs of every method and property node
> within the diagram?
>
> A little background:
> I have a subvi which simply loads an Excel workbook at the click of a
> front panel button. First execution was fine, but any subsequent
> execution caused the workspace of the workbook to appear transparent
> (I could see the Windows desktop through the Excel Application). When
> I looked at my diagram, I found that I was closing the workbook refnum
> but not the Excel._App
lication reference.

I found the same problem; not a video driver issue.

I pressed ctrl-alt-del and found that Excel.exe was still running, even
though I had called the appropriate functions to close it! Verified by
NI Tech Support in LV 5.1, still a problem in LV 6.
So if you have Excel.exe not properly closed, and you try to open an
Excel file again (either manually or programmatically) you see the
effect described.

Mark

[dambored]

Hi

There are a few simple, took many months to come up with these, rules to follow when using Excel.

1. When you are using Excel you must close all of your references in the opposite order you opened them.

2. You must not open multiple references to the same object.

3. Once you are finished with a reference close it. Don't leave any references open, if you do Excel will not allow you to properly close it.


Hope this helps

Dean

[Mike_B.]

Thanks a bunch Dean for responding. This is the one frustrating thing that is halting the completion of my application.

Again, when you say reference...you must mean every method and property node reference dupe that is often seen to be "dead-ended" in N.I. Excel Automation Examples?

If what I understand is correct, a simplified example might be... the reference dupe output for worksheet would be closed first, then the workbook reference, and last the Excel._Application reference would be closed?

My ActiveX automation can get much more involved than that. I would also need to close any reference that does not have a datawire continuity to a "close automation" function. I'm just surprised how messy these diagrams are going to get with refnum wire
s running to every deadended method and property node.

One thing that is throwing me about your message is the mention of multiple references. When I perform a single read or write to one cell, it takes many references just to perform that operation. Do you mean that it is not wise to perform several activeX operations simultaneously, using shared references?

So close, yet so far
Mike B.

[dambored]

Getting closer is always cause for celebration.

From what I have been able to deduce Excel doesn't like or can't cope with "dead-ended" references and has no internal mechanism to close references opened by remote applications. The new Word / Excel toolkit that NI has is much better at handling Excel. I haven't used it much but I have looked at every VI inside it and they seem to follow the rules of my previous post.

Its OK to keep, what I call major references open, which I classify as Excel._Application, Excel._Workbook, Excel._Worksheet, and possibly Excel._Sheets (I don't remember this exactly, and its been a year or so since I was majorly involved in excel). But make sure you close them in the reverse order you opened them. So you would close Excel._Sheets, then Excel._Worksheet, then Excel._Workbook and then Excel._Application. Clear as mud? For Minor references such as , cell, range, etc I would open references, do my work and then close them.

I'll give an example. Say that the Excel application references are open and a worksheet is open.
Say this is SubVI A

Open reference 1
Use reference 1 to open reference 2
Do something with reference 2
Close reference 2
Close reference 1

So this VI would contain to Open references and 2 Close references

Continue on to next subvi.

I have attached an unlock diagram for NI toolkit. Hope they don't mind. It a very poor jpg but I wanted the size to be small.
What you can't see is that they keep those "major references" open and this is a subvi that does something. Notice the order that the minor references are opened and closed.

I'm not sure if it will be a problem to perform several simultaneous operations or not. I would think that as long as you close the references in the proper order it would be ok. I don't think we did that.

This sort of thing is something that I would make sure that I would use lots of subvis and lots of error handling.

Hope this helps...some more...

Dean