OT Using Excel at the same time as using ActiveX interface to same?

[Ben]

Thank for reading!

I have an application that is using ActiveX calls to update 48 Excel spreadsheets.

Everything works well until the operators start poking around.

Example:

1) App is running and logging away.

2) Operator opens another Excel spreadsheet and the 48 files I am using, open in the background.

Q1 Any way to stop that?

Q2 Operator closes Excel and kills my conncections?

I am leaning toward the "Don't do that!" approach but if anyone has any suggestions that may help, please share.

Thank you,

Ben

[smercurio_fc]

Isn't it annoying how operators always manage to screw up programs, or do things you never in a million years expected them to do? I've been told they're like toddlers in that respect, though I can't speak to that since I don't have any kids.

To your query: you might want to try creating your Excel files using ODBC. I had suggested this mechanism in this thread, and Mike came up with an example. The interesting thing is that if you try to open the file from Explorer before you've closed it you'll get an error saying Windows can't access the file, which is probably a good thing in your case.

[tst]

How are you opening the connection?

My suggestion would be to open a reference to the Excel app, but not make it visible, so that the user can not close it (see the Excel macro example). Also, the Workbook class has a ReadOnly property which I believe will prevent the user from saving to that file if you set it to T.

[Jarrod_S.]

Q2: You could probably handle the user closing the application by registering for the ActiveX event that the app is closing. This won't prevent the user from closing it, but if they do your app will be aware of it and can re-establish the connections and possibly buffer the data until then.

Is your instance of Excel visible? Or do the users manage to open it up simply by clicking on Excel in the Start menu?

Message Edited by Jarrod S. on 06-19-2007 05:51 PM

[Ben]

I open it not visable and they start it from the strat menu.

Ben

[Wayne.C]

Ben,

What version of Excel are you using? 

Are you creating 45 worksheets in a single workbook/file or 45 individual files?

[tst]

Or, as we would normally say - show us the code.

[Ben]

Sorry been on site all day.

48 files.

I do not have the code here, maybe Friday?

Ben

[shoneill]

Well, I don't know what version of Excel you're using, but whenever I open a hidden instance of Excel and the user opens Excel from the menu or a shortcut or whatever, a second instance is created, independent from my version.  I use Excel 2002.

Problem with hidden instances is when bugs rear their head and the files don't close dut to out-of-order reference closing.  Then you end up with X zombie Excels in memory.  Task manager can take care of that though.

Otherwise, when accessing things in Excel, try to use absolute references, not relative.  Relative references like XLDown selections can be screwed up if the user clicks somewhere at the wrong moment.  By avoiding working with selections at all, screen updates are kept at a minimum, and the code actually runs a lot faster.  The same holds true for Macros.

I'm also the opinion that files can be hidden.  This is of course, yet another possibility.

Shane.


PS Are you going "full" ACTIVEX (opening everything from an application reference) or are you working with embedded documents?
PPS When opening an application reference, there's a boolean input "Create new instance".

Message Edited by shoneill on 06-21-2007 12:24 PM

[Ben]

OK, finally back in the office.

I can't post the source code due to blah, blah, blah...

But I will share some pictures.

The Excel file writting is handled by an action engine.

 

init.png

In the Init action I open Excel (on the local machine. Anyone ever open it on another machine?) and cache the reference. Arrays are also allocated for use latter.

Open Excel just opens a ref and the default for visable is false.

 

Open_Excel.png

At update time, the "Update action is invoked and I pass in the data, index, and DUT name.

Update.png

The Update Sheet VI locates the proper refs for this DUT and adds updates the sheet.

Update_Sheet.png

Ensure_Proper_Sheet_Open checks if the proper work book is open and opens it if is not. It also closes workbooks if the DUT has changed.

Ensure_Proper_File_Open.png

So if I decide to try to fight with the operator if Excel is open, I could check the validity of the "Excel ref" before proceeding, but the customer has not yet decided if they want me to change the code or if we will take

another approach.

So that is what I have.

Thank you for reading!

Ben

Message Edited by Ben on 06-22-2007 10:02 AM