How to open Excel Template save it under a different name and then write and save data to it at regualar intervals

[reesethelabviewguy]

I have an excel template that I have created. I am opening that template, saving it under a different name, and then writing and saving data to that excel sheet at regular intervals. It is giving me an error 5, I understand what this means and I am trying to work around it.  However after too many hours spent trying to figure it out, I have asked for any help or input. I have attached an example vi, not the actual one since it is very involved.

Thanks

[Jeff@VMI]

At the very least you have to move the save file step outside of the for loop.

[RTSLVU]

On a side note do this.

 

Not this

 

[ben64]

I cannot reproduce the error using the vi you attached (using LV2014 with report generation toolkit 2012 sp1) with an empty template file.

To add to the comment, you should make your template an .xlt file which automatically create a copy (no chance of overwriting the template file).

Ben64

[mikeporter]

Better yet, build the file name using a single format to string node and use a conventional build path to add it to the path.

Mike...

[Bob_Schor]

This snippet encapsulates most of the previous suggestions, and adds a few of my own.

The first code shows one (simplified) way of building the output file name.  It incorporates the Build Path function to combine Report File Path with the file name (don't need initial "\"), builds the File Name with Format into String, getting the Time part of the name from Format Date/Time String.  I also use Build Path to get the Template path.  Inside the For Loop, another Format into String gets the data that is placed in the 10 Excel cells.  We don't write anything yet -- we're only filling in the cells in the WorkSheet (think of how you use Excel -- you could, but probably don't, save the WorkBook after every cell entry, you wait until you are all done and then do a Save, followed by closing Excel).  Finally, when we are done, we save the file using the output name we want to use, then close Excel (which disposes of the Report Object).  If we want to generate another report, with another (time-based) name, we can put this code into a sub-VI and simply call it again.

Don't worry if you don't have LabVIEW 2014 (which was used to save this snippet) -- most of the code comes from the original that you posted, so it should be a pretty simple edit to change that code to match this.

Bob Schor

[mikeporter]

You know you can wire a timestamp to a format to string and move the time formatting into the format string.

Mike...

[reesethelabviewguy]

I understand that I can save after I put in the information and it works fine, I have tried that. However the question was how can I save inside the for loop. The problem is I am running a test that is going to be running over the course of several days. Essential each iteration is a new measurement and write to excel, and I want to save the measurement to a network drive in Excel so that it itsn't deleted in the case of a power failure.

I appreciate the advice on the time stamp and thanks for all your replies! I appreciate the help. 

[mikeporter]

You can save to the file inside a loop (as Bob so clearly demonstrated. The only issue might be how fast you need to write the data. Writing to excel is not a speedy process.

[Bob_Schor]

I don't quite understand your recording scenario.  Pretend that I'm trying to do this manually, say once every 5 minutes.  The question is, what do I do every 5 minutes?  

1. pen Excel, enter data into blank WorkBook, save with unique (Time-Stamped, perhaps) file name.
2. Open existing Excel WorkBook, add data to the end of the WorkSheet, then close the WorkBook.
3. Having pre-opened Excel, add data to the end of the WorkSheet, save (but don't close) Excel.

Do you see the distinction?  Scenario 1 is basically what I outlined -- as often as you want (every 5 minutes), you open Excel, take some data, and save it as a unique file.  You end up with many WorkBooks, each holding one session's readings.

Scenario 2 gathers all of the readings into a single WorkBook.  For safety, you open and close the WorkBook before/after each reading.  Like the first Scenario, there is the "cost" of opening/saving/closing Excel for each recording session, plus "saving the data" at the end.

Scenario 3 puts the opening/closing of Excel outside the loop.  I'm not sure if you can do the equivalent of File/Save with a LabVIEW function, and am also unsure what would happen if the PC "crashed" after a Save -- the data would probably be OK.

BS