Obtain the reference of a worksheet for an already opened Excel workbook

[Ray.R]

I've been reading many posts on using Excel, and Active -X with Labview.  There's lots of info out there.

However, I am probably implementing a different "twist" to typical useage...

Here goes (I hope my description is clear)...

There is a main VI which calls a number of test vi's using a "Call by Reference Node".  This means that the vi's are not part of the main vi, but called by name from a list which is generated from selections made on the Front Panel.

Just before calling / running the tests, a Excel Spreadsheet is opened and Worksheets are created (added) and populated with appropriate information.  The Excel Application, the Workbook and the Worksheet References are kept OPEN until all the tests are done and the operator Exists the main vi (stops the sw).

References to these are passed to the running VI and everything is fine to this point.

Each test vi can be run on it's own since they are Engineering Tests.  Any test vi can obtain the name of the Excel Spreadsheet from the main vi as well as the Excel Application Reference and the Workbook Reference from the main vi, even after it has terminated (Exit was selected).  I placed a Case Structure to prevent closing the references at the end in order to be able to debug what I am about to describe below.  All references should be available...  Actually, I do not really care that references are available or not, here is why:

I do not particularly care that the references are still available.  What I want to achieve is this:

- Open an Excel Workbook and worksheets, and leave everything open.

- Be able to access the worksheets from the already opened workbook WITHOUT having to re-open the worsheets..  They should still be opened, right?

- Use / access worksheets without having to open / close / open everytime the worksheet is used. 

I tried to use the Worksheet Open Invoke Node, it returns 0 (NULL) reference.

At this stage, I'll be happy to access an already opened Excel Workbook!

I'm sure there is a way to do the above..  🙂

Thanks,

RayR

😄

[Ray.R]

Picture is worth a 1000 words.  Attached is a screen capture of the reference info.

I tried using the ActiveX Automation to get the reference..  no success yet.  (still digging).

Message Edited by JoeLabView on 12-05-2006 10:09 AM

[ravis]

Not sure if thats exactly what you want but you can access already opened Workbooks/Worksheets by using their Number.
The example in my screenshot opens the first Worksheet in the first Workbook and reads cell A1.

[Ray.R]

Thanks ravis,

Your information is good; however, it is not quite what I am looking for. 

Can references to an Excel worksheet be used (shared) among vi's..  More to the point:  If you open a spreadsheet reference in one vi, then share that reference to be used by another vi, should it point to the same object (ie Cell, Worksheet, etc)?

I am using LV7.1 and seeing something bizzare when sharing a reference to a cell. 

A Main VI creates a spreadsheet by using a sub-vi.  It basically creates a template.

The test vi sets values within 2 worksheets.  One holds values which points to where active data is being written to in another worksheet.   Remember that each test has its own worksheet. 

In the test VI, there is a vi which obtains the value to where data is written to in the worksheet.  Another vi increments this va;ue to point to the next row.  BOTH of these vi's share the same reference to a CELL within a tracker worksheet.  Well, when writing the value to the cell, it places the new value in the wrong location.  In a location that doesn't make sense.  So, I gave up on the Cell reference idea and implemented a solution which points to the worksheet and from it I select the cell.  The value does not change!!!  As a matter of fact, there are no errors generated and nowhere in the worksheet do I find the newly written value...  (I have probes all over the place within all the vi's concerned).  The value does make it to the Range>Value Property Node.  And this same implementation works elsewhere...

... with one tiny exception...

Where it works is when the worksheet is opened and does not use a previously used reference...   So is it my imagination or can references be re-used??  Oh and by the way, the main vi is calling the test vi in a loop, so it's references are still active (ie temperature tests).

????  confused ????  😮 😮 😮

-sigh-

RayR

[Ray.R]

D'OH!!!

I may have found the culprit...

I was using the Invoke Node > Reinitialize All to Default <...  It re-initialized the refrences!!!  D'OH!!!! 😮 😮 😮 😮

(I hope there some coffee left ...  I obviously need some)  😉

😄

[Joe_H]

Hey JLV when you find the solution could you post it so that we could link this post to the excel board.

[Ray.R]

Thanks ravis,

As a matter of fact, the references change, and it appears that they cannot be re-cycled... or rather reused for the same worksheet.

I re-visited your example and ran it multiple times, each time a new set of references was created, as expected.  That part I was aware of.  I thought once you have a valid reference to a workbook, its references to active worksheets could be used multiple times.  It does not appear to be the case (unless I missed something somewhere).

I am changing the implementation to close worksheet references and obtain new ones for each vi / sub-vi using the sheet.  So far, the workbook reference seems to be okay to share..  I will post the next outcome..  🙂

RayR