Adding Worksheets Question

[Sundown]

So this is code i have that saves currently one array from data generated by my program to an excel file. It then saves and closes excel.

Below i just generate a path for the file to save to. So my question is really that I also have three other graphs that are generated in adition to this chart. How and where can i add in code for these other graphs?

I've been looking but havent seen much in the way of adding more worksheets to a created workbook. Any guidance or examples would be appreciated!! Thanks!!

[tst]

First, I would suggest creating subVIs which would make this more readable and allow you to reuse them. For example, the middle part could be a VI called "Add data to sheet" (and could probably also be implemented differently).

Second, for more information on Excel coding, I would suggest recording a macro in Excel, doing the operation you want and then looking at the resulting VBA code. Usually, it should show you how things are done.

Third, you can try figuring it out yourself. For example, I'm assuming that for the Sheets property you get in the middle, you can call a New or Add method, which would create a new sheet, which is what I understand you want.

Fourth, you can always try searching Google, as there are many people in the world who work with Excel.

And last, you can probably find some code and advice which will help you in what people already posted here -

http://forums.ni.com/t5/BreakPoint/Excel-Board/m-p/379682#M2391

[.aCe.]

Do you have report generation toolkit for office (it comes bundled with developer suite)? https://www.ni.com/en/shop/labview/select-edition.html

If yes: http://search.ni.com/nisearch/app/main/p/bot/no/ap/global/lang/en/pg/1/q/excel%20graph/

If no: follow the tst's post!

The code as you can see has nodes to quit the application, so I guess you would add your add chart subVI before that.

[ben64]

You need to get a reference to the active workbook worksheets collection, then use the sheets add method.

Ben64

[ben64]

Now that I have acces to LV here is an example.

Ben64

[Sundown]

Im waiting for a PO to go through but I will have the report generation toolkit eventually. Im trying to just come up with something to do the job until then. I think my problem is i dont quite understand when to close versus adding data... So do i have to close the reference to the worksheet and range before adding another? The last post about how to add a new worksheet helped, but I just need some direction on how to add data from 4 seperate arrays to 4 seperate worksheets.

I have looked extensively through the forums and on google but havent been able to find anything of this sort being done before.

thanks all!!

[Allison_M · Accepted solution]

Hi Sundown,

To answer your first question: you do not have to close references to the worsheet and range before adding another, but if you decide not to close the references you will have to keep track of all of them in an array and close them all at the end, otherwise at the end of your program you will have references that are not closed. The better way of implementing this is to put part of your code in a loop, and close the references in the loop. Your Sheets' property node has an index input, and that specifies which worksheet you are getting a reference to.

If you put a loop around everything from the sheets property node to after you write the data (the range property node), you can open references to each one of the worksheets and write to each one. Move the relevant close reference VIs inside your loop so that you are closing the reference to your specific worksheet and your range during each loop. 

Your loop will run four times (once for each array and each workbook) and you can wire two auto-indexing arrays into it, one array of the worksheets you want to put the data into (eg {1,2,3,4}) and a second array that is an array of your four arrays that you are writing to the worksheets.

I hope that helps, if you find this confusing, please let me know and I can try to make a quick example image to show you what I mean.

[Sundown]

Great explanation!! I think I have it working now... I just wrote it very similar to the way you explained. I didnt realize exactly how the references work before, but i think I have a better idea now. Thanks so much for everyone's help!! I really appreciate it!!