Dataset export to Excel template

It would be nice to be able to export from a dataset to an Excel template (eg in order to have a graph automatically created on the Excel spreadsheet).

Thanks.

If you are not afraid of a bit of VBA programming you can create this functionality easy enough. One method would be to

  1. Create your Excel template that graphs some data range (I’ll call it “myTemplate.xls”).

  2. Save the dataSet to a temp Excel or csv file to a hard coded location and file name. For example to c:\Temp\myData.xls

  3. Create another Excel workbook called “myCode.xls” for instance. Then write a macro subroutine (call this subroutine from the Auto_Open() procedure) that creates a new workbook from myTemplate.xls. Then opens myData.xls, copies the data out of it and pastes it into the the graph data range of your new workbook. Closes and kills myData.xls. Then finally closes itself. What should be left on your screen is your new workbook with a pretty graph.

To make it all work from Ignition, create the temp file as you have been doing (but not open Excel doing it). Then open myCode.xls using system.util.execute.

It might seem a bit cumbersome but it works pretty well. I used to do something almost exactly like this in Wonderware to create automated reports.

Thanks a lot for sharing, I’ll try that out!

No problem, feel free to shoot me a message if you run into any hiccups with the VBA end.