Problems with Excel and ActiveX

[postformac]

Hi,

I know there have been a lot of posts on here relating to Excel and ActiveX but I couldn't find anything that showed exactly what I am looking for.

Basically I am trying to write an array of data to an excel file but there are a number of steps that I want to go through first, I can open the reference to Excel, create a new workbook, create a new worksheet and write my data but what I can't manage to find is how to copy an existing worksheet to a new one and re-name my new worksheet. I have no concept of how ActiveX works, I have basically just copied what I have found in other posts and LabView examples so far....

What I want to do is the following:

1. Open an existing workbook (template)
2. Copy the first worksheet in that workbook (which will have a template layout and set calculations)
3. Paste as a new worksheet with my array of data added in the correct place
4. Rename the new worksheet to a name created in my program
5. Repeat for a number of further worksheets with further arrays of data
6. Save the workbook by a different name to preserve the template file

In my VI at the moment I have tried to use the "copy" method for the "excel._worksheet" class which does copy the correct worksheet but then puts it in a new workbook instead of adding it to the existing one and my array is added to the existing worksheet rather than the copied one. I don't seem to be able to put any input into the "before" or "after" nodes on the _worksheet invoke node, any data type that I connect to it gives me an error message and stops the program from working.

I have looked at the Excel object model map here http://msdn.microsof...y/bb332345.aspx but I have no idea what it means, is each column a subsiduary of the previous one or what? I don't get what I am looking at.

I have attached my files so far, could someone give me some pointers on this or show me where to get further information on how the whole ActiveX thing works?

Thanks

Write Excel simple.vi

Write Excel.vi

Template.zip

Edited February 9, 2010 by postformac

[ooth]

Ok here's how I would do it. This example might confuse you even more because it uses a lot of pre-made VIs instead of just showing you the raw Active X code. But you can open up the excel copy worksheet.vi and look how I'm doing it.

1. Unzip the Excel VIs to your user.lib and probably do a quick mass compile on them.

2. Unzip the Copy Example.zip to anywhere you want. It has the main Excel Copy Example.vi and 2 Excel workbooks: your Template.xlsx and a Final.xlsx.

3. Run the copy example and the Final workbook should have the template sheet in it all renamed and everything.

4. Step thru the example to see how it works. The excel copy worksheet.vi is what does the magic that you want. The problem is that it has a bunch of subVIs. But at least you can see what I'm doing.

Good Luck. I'm sure you'll have some questions.

-John

Excel.zip

Copy Example.zip

[postformac]

Thanks very much for the reply, I have re-arranged your program a bit to make it better suit my existing program that I am adding it into but basically you have given me exactly what I needed.

The Excel VIs give all of the functionality I need without the hassle of having to do the ActiveX stuff myself, much easier! The only thing I can't find a VI for is to delete a specific worksheet, is there another way to do that? I just want to remove the blank original worksheets after I have finished adding all of my new ones.

Are these VIs part of the NI report generation toolkit or where are they from?

Thanks again for the help, I've been struggling with that whole ActiveX thing for a while!

[ooth]

You are correct there is no Delete Sheet.vi but I made one now. I usually make VIs as needed. Just insert this new VI into the library.

I found most of these Excel VIs on NI Forum under the Excel Board. I think they are a good alternative to the Report Generation Toolkit that NI sells. I didn't create them but I did do a lot of modifications so the ones you see there are different than the ones I have posted.

excel delete sheet.vi

P.S. The excel select sheet.vi also inserts a new sheet or selects a sheet. I doubled up the functionality on that VI.

[ooth]

Oops, forgot to change the VI description. Here's the new delete sheet.vi

excel delete sheet.vi

[ooth]

There's one caveat when using these VIs. Say you are debugging your program and abort it before the excel close.vi gets a chance to close all the Active X references. Well next time you run the program the VIs will probably start to error out and Excel won't open. You should open the the excel DESTROY ALL REFERENCES.vi and run it first before starting the main VI. In fact a put that VI in the initialize stage of all my programs.And for debugging purposes I just have it open and off to the right. The VI is found in the Low Level palette.It's the one with the red X on the Excel sheet.

[postformac]

Hi ooth,

Thats brilliant, thank you very much for your help and time.

I had noticed the problem with leaving references open and ending the program half way through, so I have added the destroy all references vi at the beginning of my vi as you suggest.

One thing with the delete sheet vi, at the end of my program I am left with an open copy of excel that has a message window asking the user to confirm the deletion of the sheet(s). My program stays running until the user either confirms or cancels the delete operation. Is this correct? Its not a big problem obviously.

Thanks

[ooth]

I didn't notice that Excel prompted the user when deleting a sheet. If that's the case you might have suppress alerts. Look at the Save Workbook.vi to see how it's done. I'll be away for most of the week, but I'll look into it when I get back on Friday. You probably will have figured it out by then. Good Luck.

[postformac]

Ok I got it to work with supressing alerts after following what is happening in the Save Workbook.vi as you suggested.

I had to alter one if the VIs to give me the correct refnum as an output though, I couldn't put the reference that is currently output from all of the Excel VIs straight into my property node as it is an "excel object reference" and I needed an "Excel. _Application" reference. I don't really get the difference so I'm not sure if I have done the right thing there, it seems to work fine so its not really a problem but I just thought I would ask if there is a better way to do it like convert the reference or something.

Thanks

[ooth]

I'm glad you figured it out. Whenever I need a specific refnum in my main VI I use the excel Get Data to Modify.vi. It's usually the first subVI in all the excel VIs and is used to access the "excel object reference". I didn't make most of the VIs so I can't explain much. If you drill down to the excel objectRepository.vi you will see that it is locked. But I don't care much because I've been using these VIs for a while now and they work for me. I don't have to know every in-and-out of the workings.

Here's an example of pulling out the a specific reference. Just be careful that you close the references that you pull out before you execute the excel close.vi. I didn't close the Application refnum in this case because the excel close.vi will do that for me, but if I proceeded to create a range refnum from the application I would have to close it before continuing on to the the excel close.vi

[Bob Schor]

I came here three years later following a post in the NI LabVIEW forums.  I also had developed a set of ActiveX routines to "do things" in Excel, but found it tedious to get the documentation for the ActiveX functions, particularly with Windows 7 and LabVIEW 2012 (looks like Microsoft is moving away from ActiveX, anyway).

 

I "bit the bullet" and tried using the newer LabVIEW Report Objects, and life got much easier.  [Oops, can't figure out how to paste snippet in here, so I'll try to Attach my VI].  Hope this is helpful.

 

BS

EXCEL Delete WorkSheet.vi

UTIL Ask Sheet Exists.vi