Adding a new sheet to an Excel workbook from a templete

[PhoenixClaw]

First post here I think.

Ok so here's what I'm trying to do: I have templetes that I write data to for calibration and acceptance testing based upon past numbers, the file being saved as a serial number named xlsm file.

So, if the calibration or acceptance test for that serial number has not been done before, the file dosen't exist, so I make a new one from a templete, fill it and save it. That's the easy part. The part I'm stuck on is the requirement that any subsiquent testing done upon that serial number should have the reports added to the original file as a different tab. If it was only as easy as right clicking in Excel and adding. So, how do I insert a new worksheet into an existing workbook from an existing templete (that I can then fill with data)? I've scoured searches from all over the web and here and tried different examples and suggestions and I'm just not good enough right now to figure it out. I'm beating my head against an activex wall that I'm trying to learn as fast as possible. So, any suggestions or nudges in the right direction that anyone would care to give? I'm not asking for it to be solved for me (although I wouldn't mind!) but some help would be greatly appreciated.

Some additional info, I'm using LabView 2014, and using xlsm files. I just need to pop a new sheet into an existing workbook from a templete (which whould make it the first tab) and then I can populate it easily.

Thanks in advance from a rookie!

*edit* typos, ugh

[ben64]

Adding a worksheet to an existing workbook is the easy part, the following code will do it:

The difficulty is the use of the template, here is how I would do it:

1- In any case use the template and fill it as usual.

2- Before saving the filled template check if a workbook already exist for this serial number.

3- If it doesn't exist then save it.

4- If it exist use the copy method of the sheet usedrange property on the template you just filled (this will copy the sheet content to the clipboard)

5- Get a reference to the worksheet you just added to the existing workbook and use the paste method on this worksheet (this will paste the clipboard content) You can alternately look at the destination input parameter of the copy method and use the new sheet item at this input (never tried it).

6- Save the workbook and do not save the filled template.

Ben64

[PhoenixClaw]

Thanks for your reply Ben, I appreciate it and apologies for the length in time to reply to this as I had a very hectic weekend.

I'll give your suggestions a try and see if I can make it work!

T.J.

[Bob_Schor]

If you use the NI Report Class version of the Report Generator Toolkit code, adding a new Worksheet becomes very simple.  I wrote a VI called "Return Worksheet" that sets the Current Worksheet to be the Worksheet that you specify, creating the sheet if it doesn't exist.

[PhoenixClaw]

Hi Bob, thanks for your input.

I solved this, kinda. What I ended up doing was immediately copying the templete within the same file, renaming the copy, bringing it to the front, and filling the copy while leaving the original templete at the end. Everytime a new test is run it will just copy the templete and fill it, leaving the most recent test at the front and the templete will always remain at the end. So, not what i had originally intended but functionally the same.

Thanks again for the input, it did help with the understanding the activex stuff!

[Hooovahh]

I know you found the solution, but just for completeness you could also accomplish this with XLR8.  A toolkit for reading and writing Excel files, without needing Excel.

http://sine.ni.com/nips/cds/view/p/lang/en/nid/212056