Report Generator MS Excel Add Worksheet with template

[Allen_Rode]

I’m attempting to open a specific Excel template using the Report Generator toolkit. I also need to add Excel worksheets with blank templates depending on how many sample will be tested. The initial worksheet loads with the template. The issue I’m running into is when I add a new worksheet the added worksheets are blank. I attached a copy of the Excel template along with a test VI. This will be used in a larger Object Oriented program that is being modified. This is my first time using the Report Generator toolkit so I assuming I’m missing something. I did browse through the forums but didn’t see anything specific to what I need.

Thx,

Allen

[Bob_Schor]

I have Good News and Bad News, and I don't know which is which!  Here are some random loosely-connected Facts:

• Excel Templates (.xltx) are not exactly what LabVIEW means by a RGT "template (empty)" on New Report -- this input is much more like the existing file for File Open.
• In Excel, you can set a Sheet Template that will be used every time you do (in Excel) New Worksheet.  This template needs to live in the XLSTART folder, by default (in Windows 7) in <User>/AppData/Roaming/Microsoft/Excel/XLSTART, and must be named Sheet.xltx.
• If you copy your  Sample_DataSheet.xltx to this folder and rename it to Sheet.xltx, you do, indeed, generate new Worksheets that match the template.
• Because all of these files and folders can be manipulated by LabVIEW, you could write a LabVIEW Utility to replace the default Sheet.xltx (by default, there is no such sheet) with your own sheet.

Just for fun, I spent about two hours creating a group of sub-VIs to allow me to "nicely" manipulate the default Worksheet template, as alluded to above.  I then put my Default Sheet VI "in front of" your code and gave it a try.  Can you say "Big Disappointment"?

I'm at a loss as to the difference between doing Add Worksheet in LabVIEW (which uses ActiveX) and doing the same thing from within Excel "by hand".  I notice that Excel calls the manual operation "Insert Worksheet", but don't really think that's the issue ...

I'll keep thinking about this and following this post.

Bob Schor

[ericthemantis]

Hi Allen,

While there is not a specific function in the Report Generation Toolkit for creating a new template worksheet, there is a community example that demonstrates a way to do just that.

Copy a Template Worksheet Using the Report Generation Toolkit

https://forums.ni.com/t5/Example-Code/Copy-a-Template-Worksheet-Using-the-Report-Generation-Toolkit/...

The code is creating a blank spreadsheet as before, but then loading in a tab-delimited version of the Excel template that can then be written to the blank spreadsheet. Hope this helps!

-Eric

[Allen_Rode]

Well at least my own pride is not so badly deflated. Just to make this a bit more complex we are using named ranges to load the data into Excel from a spreadsheet.ini file generated by LabVIEW. We wanted something that could be viewed by the operator in LV but would not allow the operator to delete or change the data while in test. Post test results are written to the Excel file and saved along with a PDF file for the operator to print. All works great on the first sheet... then ensuing chaos takes hold.

The spreadsheet.ini file read by columns, which is the test data, not rows, which is the sample ID#. I need to control the datasheet length by row. Typically there are 20 samples per datasheet and lot sample sizes can range in the 300 to 500 range. I think I’m going to try the print and close individual datasheets. We are generating date / time stamps as part of the filename. I was hoping to have just one large file to print verse 20 plus individual files. The Excel datasheets don’t look so well but the PDF datasheets are okay. A bit of a band aid fix but will do for the moment. The plus side is the data is being hand written so automating this is still a substantial improvement in time and accuracy.

Thanks,

Allen

[Allen_Rode]

Hi Eric,

What we are attempting is to use a predefined Excel template for each worksheet. The final Excel file will look similar to the attached picture. The hope is to populate the datasheet using the same format as the handwritten datasheet. This is what the program is generating now. We just need it to continue to the next sheet.

Thanks,

Allen

[ericthemantis]

Hi Allen,

There is a tutorial that goes through some of the basics of the Report Generation Toolkit located here - 

https://knowledge.ni.com/KnowledgeArticleDetails?id=kA03q000000x3gcCAA&l=en-US

For more advanced manipulations of Excel Spreadsheets with LabVIEW, there is a set of Excel Macro VIs within the Report Generation Toolkit that you can use to run macros on your new report. You could write a simple "copy worksheet" type of macro in VBA, then run it on your report in LabVIEW, or any macros for that matter. There is also the option to modify the new report by using ActiveX calls in LabVIEW. This white paper talks a lot about the different things you can do with ActiveX in LabVIEW with Excel workbooks -

 https://www.ni.com/en/shop/labview/activex-and-labview.html

Best of luck with your application!

-Eric

[Bob_Schor]

Well, I haven't yet tried the Macro Route yet, but I have tested the "set the Default Worksheet Template for Insert Worksheet", that doesn't work from LabVIEW (though it does work if you open Excel yourself and click "Insert Worksheet"), and have also tried to do this using ActiveX calls from the RGT, that also hasn't (yet) worked.

I'm currently at NI Week, have asked a few people who've also done a lot of Excel/LabVIEW work, noone has tried to do this, so I still don't know if/how to do this from LabVIEW.

BS

[ej · Accepted solution]

Allen,

I coded an example for you to demonstrate what you need to do.  I'm currently at NI Week and didn't have LabVIEW 2014, so I didn't get to work on your code directly (I used LV2013 instead).  The key is to replicate the sheets that you need before modifying any of their cells, and wiring the reference of the worksheet object that you want to copy to the Before or After option of the _Worksheet.Copy method.  If you don't wire to one of the options, Excel creates the worksheet in a new workbook.

Hope this helps,

Eric J

ViArch

P.S. I also attached your original Excel template.

[Allen_Rode · Accepted solution]

Hi Eric,

Thank you very much. The sample code works perfect. I’ll implement this in and see how it works.

Thanks again,

Allen