Create a custom named copy of Excel Template and write Data to the template

[sachin.davis]

Hello,

I am working on an application that can write data onto an excel file. I can currently write the data onto a .csv file and everything works great.

The issue I have is that I need excel to plot graphs automatically using the data

Is there a way where i can create a copy of an excel template i create and rename it to something that the user chooses from the front panel and then just write my csv data onto it.

I know there is a MS Office Report.vi that i can use but that doesn't meet my file naming requirement and for me ActiveX doesn't work either. While i am able to run the examples, i am not able to change the methods in property nodes.

Please let me know if there's a solution meeting my needs.

All help appreciated!

(Using LabVIEW SP 2021 version)

[GerdW]

Hi Sachin,

see this other discussion from today.

It shows how to load a template and save to another file…

[sachin.davis]

Thank you for pointing me in the right direction. There's one more thing i would like to clarify. Is it possible to not open the excel file but do everything in the background as i noticed that the create report opens an excel file and writes data onto it as seen in the linked example.

[sachin.davis]

To bring clarity to my exact problem.

I am able to create a copy of my template but when values come in they do not follow formatting and i am unable to plot the data correctly

Here's the VI and the template file i need. 

I have also attached the blank file my VI generates

LabVIEW SP 2021

[Bob_Schor]

There is a "Revised Generate Excel Report" in the LabVIEW Forums (use the Search Bar at the top of the Forum window and type in "Revised Generate Excel Report" and you should find it).  This doesn't use a Template (but you already know how to do that), but does show how you can "add rows and columns of Data" and even "add an Excel Graph" to a blank, or a Template, Workbook.

A key step in this Demo is that it uses the Excel Easy Table function, which gives you a little better control of things.  Try it to see what/how it works, then modify to suit your needs.

I did not quite understand what you meant by "when values come in they do not follow formatting".  LabVIEW tries to write the data in the form that it sees, so Floats may have 5-7 digits of precision, while integers will be whole numbers.

Note that there are three sets of columnar data.  The first set (Date/Time/Operator) starts in the default Upper Left cell.  The Easy Table gives us the Row/Column of Next Cell Top Right, the column in incremented , and the Test/Humidity/Pressure data are written to the right.  To get the longer column of data, the Row/Column of Next Cell Bottom Left (from the first Date/Time/Operator) is plotted below (with a blank line skipped).

Even though this demo is eight years old, its author might still be available for questions ...

Bob Schor

[sachin.davis]

add an Excel Graph doesn't work for me. It produces an error. So i am trying to use something that doesn't use too many add-ons.

What i meant with not following formatting is that my template has a format but when appending to the report using my vi it creates new data instead of actually appending values into my pre-formatted excel.

[GerdW]

Hi Sachin,

---

@sachin.davis wrote:

add an Excel Graph doesn't work for me. It produces an error.

---

Which error do you get?

---

@sachin.davis wrote:

So i am trying to use something that doesn't use too many add-ons.

When you want to communicate with Excel then you need to use the RGT functions or you do the same "on your own" by calling the same ActiveX functions as are used within the RGT functions…

---

@sachin.davis wrote:

What i meant with not following formatting is that my template has a format but when appending to the report using my vi it creates new data instead of actually appending values into my pre-formatted excel.

---

When you "append" data to an existing sheet then you create "new data" - I don't understand what you try to express here…

Btw. it would help when you attach your VI after downconverting to LV2020 (File->Save for previous). Not all of us are using the latest LabVIEW version yet, including me…

[ben64 · Accepted solution]

There are a lot of things wrong in your vi (the event structure timeout should be type I32, stop button should have its own event case, cancel button to start acquisition, ...) but the main reason it doesn't work is because you are overwriting your formatting. The use of a column header make no sense here because there are column header in your template, my guess is that you were trying to use this input so that the data will start at row 1.

The Append Table to Report.vi have an input name MS Office parameters (it is specified in the link provided by GerdW). The default position for an excel report is (0,0), create a constant and set it to (1,0) and it will work ... but it will still be a bad way to do it. By using Insert into Array you have a growing 2D array that will use more and more memory (you will be re-writing the whole table from position (1,0) at each iteration.

Instead use the Excel Easy Table.vi (I think Bob suggested it) and change the writing position by using the start input and next-cell bottom left output. The Excel Easy table accept numeric table data so there will be no need to convert to string.

I added the original vi and the modified one in version LV2020.

Ben

[Bob_Schor]

The reason to mention a Chart/Graph in the Revised Excel Example was that your Template appeared to have an Excel Graph as part of the design.

I took another look at your Template.xlsx file and saw that you had a "Quick Analysis" function in the upper left hand corner of the Template.  I don't think that this "advanced Excel" feature is implemented in the RGT.  It is easy enough (as the Revised Example shows) to over-write these cells with a Column Header row and then rows of "pure data", and even add extra columns if you want "processed data" (which you would, of course, process in LabVIEW).  And, if you want to embed a Plot in the Excel output, the Revised Example shows you how to do it.

You mentioned that putting data into an Excel template seems to overwrite the Cell Formatting you wanted for the SpreadSheet.  Note that the Excel Easy Table function (which I recommended) has an AutoFormat input (default = none) that you could explore -- I've not used it, but I think it selects from a subset of Data Formats, one of which might be what you are seeking.  Write a little test code and find out (you can come back here and tell us what you find, a little "giving back" to the Community).

Bob Schor

[sachin.davis]

Thank you both ben and Bob for your inputs. The program I attached here was not meant to be in my final application. It was just to give an idea of what I am trying to accomplish, hence the incorrect placement of the button and false labeling. However I found that the use of Easy excel table as one of you mentioned is much more efficient than the one I had and the fact that I was able to control the location of insert really helped the project. However it did not actually solve my problem of formatting a graph in excel but i wrote a custom VB code in excel macro to do that and the project is on track now. 

Once again, thank you for all your help!