Excel template Graphs and reporting toolkit

[QFang]

Hi

Trying to get some custom Excel reports out the door, but I'm running into brick walls.

After reviewing various documents I found on NI.com, such as the "LabVIEW Report Generation Toolkit for Microsoft Office User Guide" March 2004, 323805A-01 and "Report Generation Toolkit for Microsoft Office User Manual" April 2001, 322880A-01, I decided that it would be best to proceed by creating an Excel template.

On page 4-3, the User manual states that "if you need to format your graph in a way that is not supported by the formatting VIs, you can insert the graph in a template, format the graph the way you want, and use the [Excel Update Graph] VI to fill the graph with data."

Seemed very straight forward, so I spent some time formatting an xy-graph in Excel (see attached template).

When I went to fill the graph from LabVIEW, I keep getting an error code, and even looking online I can't pinpoint if the data is in the wrong format, length, orientation or what.  I've tried the obvious permutations of data alignment, with and without row/column string arrays and with -1 as well as 0 as the indexes but with the same error as results.  I noticed the help-file for the "Excel Update Graph VI" has the following note:

"Note  The Excel Update Graph VI only updates graphs you create using the LabVIEW Report Generation Toolkit for Microsoft Office. The VI does not update graphs you create in Excel manually."

The way I read the note is in direct contradiction to the user manual, but seems consistent with the error I keep getting.

Creating a graph from within LabVIEW does not allow me to quickly and easily configure the chart to the size/setup that I want to use, and several options are not exposed by the VI framework, or if they are, I have not found them.  I also have not found a way to apply an Excel "chart template" style via LabVIEW. 

If anyone can offer advice on how to automate producing Excel reports with specific formatting requirements for charts etc. I'm all ears.. I'm rather dissapointed in the $500+ toolkit from NI at this point. . hoping its mostly user error on my end and not a shi..pwrecked toolkit.

[FCTesting]

Hi QFang,

Did you create the original template file as a new Excel Document or did you generate the tables and graphs in a VI?

I tried creating a simple excel report in a VI, then making formatting changes that are beyond the scope of the VIs. Next I saved it as a template. Finally, I used that template with the Update Graph VI without a problem.

From the manual,

"If you need to format your graph in a way that is not supported by the graph formatting VIs, you can insert the graph in a template, format the graph the way you want, and use the Word Update Graph VI or the Excel Update Graph VI to fill the graph with data. You can use these VIs to update numerical data, row and column headers, or values and categories"

I think you are not taking the statements literally enough, "VI only updates graphs you create using the LabVIEW Report Generation Toolkit for Microsoft Office. The VI does not update graphs you create in Excel manually.". Thus you must first create all the graphs/tables/etc. using the VIs, then reformat the way you want and save as a template.

Hope that helps!

[QFang]

Thanks, that did help a little bit.

Seems like I can't use the same style of "charts" in Excel.  The ones supported by the VI's are embedded objects of the MSGraph.Chart type, while the main ones you are used to interacting with in a day to day basis in Excel seems to be just "inserted", if that makes sense.

Styles and formating interface and options are a complete subset in 2010, looking more like the old style charts/graphs we used to have in Excel 2003 and prior.

So, while that helped, I keep uncovering really annoying gaps in the tool-set provided by NI.  For example, you can set the range to autoscale or specify the min and max on the axis, but they did not bother to expose the major and minor divisions, which don't update (for me at least) if I change the min/max with autoscale set to false.  [EDIT:: updating my template to set the divisions to auto fixed that problem]

My main problem currently is that for a template to work, I need to know when I make the template how many tabs and charts to make, while I would want to keep that dynamic, but I can't easily copy the formating/object of one graph (say, one that was heavily tweaked in Excel and saved back to the template), and the VI's don't expose enough functionality to make the formating what it needs to be to make them from scratch.

Looking in the library from NI, it looks fairly straigth forward to add some of the functionality that they left out, and using the Excel Get ActiveX References.vi in the advanced palette, I may be able to use the existing VI's as a starting point to extend the functionality that I need myself..  I still strongly feel that something that NI charges for should at least have some more of these fairly basic formating options and functions exposed in their existing library.. especially since some of the lower level VI's are password protected. 

If anyone has gone down this path before and would be able/willing to share wisdom or VI's, it would be appreciated!

[SunshineDesign]

I use report generator allthe time to fill in Excel template reports. I only have to port the data into the table and viola the graph updates. By creating all the tables and then the graphs beforehand means I get just what I want in the report and the way I like it. I also spend minimal time making the test report as I only have to export the data to it. Open the template, write the data and save as excel file and close. Simple.

[QFang]

What do you do if you do not know ahead of time how many charts or tables of a certain type you will need?

-I'm looking to generate status reports for various locations.  Each location has the same types of equipment and therefore status chart/table data, however, the difference is that some locations have say 1 equipment set, while another location/customer could have 15 different installations.

What would make sense is an Excel document with the first tab as "cover/summary" for all equipment, with more detailed information for each equipment set in separate tabs, however, I'm still looking for ways to combine the power of Excel made templates with the limitations of the LabVIEW interface.

I'm about to find out if the advanced "Excel insert object" pointing to properly formated "tab content" will work.. If it does, I think I will get the best of both sides (easy insert data only from LabVIEW, with easy formating in Excel).

I'll report back with how it goes. 

[EDIT] well, Excel does what I ask it instead of what I want it to do in this case. My external file (template or regular) is indeed inserted into my report, but it is inserted like all other objects, as opposed to just "copied" into the tab.   Looks like I will have to try and figure out how exactly to create a utility VI that (using Advanced get activeX references from the report) copies a worksheet to a new worksheet or something similar.

[End Edit]

Thanks for reading!

[QFang]

So while I did not find a post for my original problem, going back to the forum again with a more specific problem may have worked much better.  Below are several links to various other Excel related threads, in case someone comes along later and looks at this one.

A forum thread that contains LINKS to useful threads. (NOT FOR QUESTIONS!)

How to copy a worksheet in Excel using ActiveX controls  communtity example.

A good LAVA thread on Excel and the free toolkit

install instructions for free toolkit

[Bob_Schor]

After developing a pile of ActiveX routines to let me basically read and write Excel Workbooks and Worksheets in a flexible manner, I was still bothered by how difficult and complicated it all was.  Then a few years ago, NI started using the Report Object and the Report Generation Toolkit became a LOT more friendly.  I've largely abandoned my ActiveX ways (except in a very few circumstances) in favor of using Report Objects.  For example, here is a simple VI to delete a named Excel Worksheet from a Workbook (something that is not so trivial to do using ActiveX) -- it uses a sub-VI (also included) to ask if the Sheet exists (which, in turn, uses an OpenG "Filter Error" to turn the -41110 error generated by doing a Get Worksheet function on a non-existing Worksheet into a Boolean (false) WorkSheet Exists output).  Note that I do use an ActiveX call to actually do the deletion, but get LabVIEW to do all the "heavy lifting".

Also note the difference in routing the Report Wire between the True and False cases of the Sheet Exists routine (this was a hidden "Gotcha!").