create and name new worksheets in existing excel spreadsheet

[perroy]

Hello-
I am running LabVIEW 6i and excel2000. I need to write data acquired in LabVIEW to new worksheets of an already existing excel spreadsheet, and also name these new worksheets. I have been able to create the new worksheets but haven't yet figured out how to name them. I'm new to activeX and any help would be greatly appreciated.
Thank you

[scraggs99]

I am attaching a short example which shows how to name a worksheet which you have created in Excel using LabVIEW and Active X.

The important thing to note, and the direct answer to your question, is the property node which has the worksheet refnum wired to it. ActiveX objects can have a set of methods and properties associated with them. An easy way to view these in LabVIEW is to wire the refnum for that object upto an Invoke Node and also a Property Node. Then right click on each and you will see that you can choose froma list of Methods on the Invoke Node and a number of Properties on the Property Node. The solution in your case is to select the Name property on the Property Node and to wire the string (constant or control) to the input of the pro
perty.

One of the easiest ways to learn what you can do with the Excel ActiveX objects in LabVIEW is explore using the method described above.

Good luck!

-Craig

[Paul_Torek]

Thanks Craig!

I have a related problem: how to add the new worksheet in a specific location, namely, the new worksheet should become the last one. I tried wiring an i32 (=3) to the "after" node of the "Add(Sheets)" method, got an error. I tried wiring the string "Sheet3" to that node, got an error.

More generally, since the nodes are all variants, how on earth am I supposed to know what type of constant or control to wire to them?

[Paul_Torek]

Figured it out through trial and error. You just use the "item" method on the Sheets object to get the sheet after which you want to add the new sheet. Wire an i32 to the index and then wire the "Item" output to the "After" terminal on the "Add" method. See the attached Open_New_or_Old_Sheet.vi, and Open_Added_Worksheet.vi.

I also attached my top-level programs Write_2D_strings_XL.vi and Write_2.5D_strings_XL.vi. Hope someone finds them useful.

[KileenC]

Hi Paul,

Programming using ActiveX can be tricky, especially knowing just what kind of datatypes the different methods and properties are expecting. There are 2 ways I would recommend for users to find out about ActiveX parameters:

1. Right-click and choose Help for the specific property/method.
When you have a method or property selected on the Property or Invoke Node, you can right-click and there will be 2 help options--one for the generic Property Node or Invoke Node, the other for the specific method or property that is selected in the node itself. If you select the help for the specific property/method, a Microsoft Visual Basic Reference Help should appear that will provide you with explanations and examples of the datatypes that method/property is expecting given the object that is in use.

2. Go to the MSDN Library >> Excel Object Model
This has an overview of Microsoft's Excel Object Model and will also show you how to use Microsoft's Excel Object Model.

Keep in mind that because ActiveX can be tricky, NI also offers the Report Generation Toolkit to make programming to Word and Excel much easier! Nice tutorials and examples are provided too. 🙂

[Paul_Torek]

The right-click trick, asking for help for the specific property or method, doesn't work on my computer. Absolutely nothing pops up. There was another computer I worked on where it did work. I wish I knew why. (I'm not a real programmer, but I play one at work.)

[KileenC]

Hi Paul,

Is your VI in Edit Mode? Make sure that your VI is switched to Edit Mode by pressing Ctrl+M or Operate >> Change to Edit Mode (if you are in Edit Mode, it will read Change to Run Mode). Also, make sure you are using the Operator (Hand) tool. Bring up the Tools Palette (Window >> Show Tools Palette), and the Operator tool is the one on the top left-hand corner. Hopefully you should be able to select the ActiveX method now.

Kileen

[Paul_Torek]

I discovered a disastrous indexing error in one of the VIs I attached before. So here is a cleaned-up replacement, in case anyone is using my stuff. Sorry about that.

As for right-clicking, yes I'm in editing mode. Perhaps it's the way Microsoft Office is installed on my computer - some lean version without the ActiveX help, maybe - but nothing pops up for specific properties and methods. I can popup the general LabView help on "property" and "method", and get results. I can also right-click and select, say, "help for Range" or "help for Worksheets" - it's just that nothing happens when I do.

[KileenC]

Hi Paul,

MS Office does need to be fully installed on your computer so that the documentation is included. This would be the best and easiest way to find out about ActiveX programmming for Microsoft applications. You can probably just put the MS Office CD back in and repair the installation to include the documentation files. Otherwise, you can also check the Microsoft website, but sometimes that can be a little tricky to navigate.

Best of luck.

Kileen

[Ben]

To get the help to come up for MS stuff, you should do a coustom install of MS Office and make sure you select

"Help for VBA" (or similar wording).

The help info is geared for Visual Basic but with a little imagination you can figure out how to structure the LV calls.

Trying to help,

Ben