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

[reesethelabviewguy]

Thanks for the reply Bob! Ok, here is what I want to do. I want to open an excel template and save that template right away as a new file saved as the current timestamp(this is so I don't overwrite the template so I can use it more than one test). After that I am going to write the test parameters into the first tab in excel. Once this is done, the program enters the measuring and recording stage. This stage last for 10000 iterations. Each iteration is a measurement, write to excel and (what I am having trouble with) save on top of the time stamped file that was created from the template. What I envisioned this save as was something similar to a ctrl+s on the keyboard, I just want to save what is being written to excel at each iteration.

So I guess the closest scenario would be 3. I only want one worksheet, not several. I also would like to steer away from opening and closing after each iteration. Like I said, I envisioned something simple like the "ctrl+s" shortcut while you are entering data into excel.

Hope this is a little bit more clear!

Thanks for your help.

[Bob_Schor]

My thought was that the "Save Report to File" function was the equivalent of Ctrl-S, but I wasn't 100% sure, so I did an experiment (shown in the snippet).  I assumed we were going to be saving our data to a file called "Test.xlsx" that was originally empty (created by copying your Template and renaming the copy "Test.xlsx").  Every half-second, we generate 4 random numbers which we write as a single row to Excel, then do a Save Report to File.  To help identify which row is which, the random numbers have the row index added to them (so Row 1 is in (0, 1), Row 2 is in (1, 2), etc.).

Some notes.  I'm using Append Table to Report, which wants a 2D table, so I take my 1D row and do a Build Array which makes a 4x1 2D array.  I also want each row to be in the proper position, so I wire the index of the For loop into the "MS Office Parameters" input as the Row position for Excel.  Note that LabVIEW Index 0 is Excel Row 1.  If you are curious how I got the little icon at the top of the Bundle-by-Name function, I first did a Create Constant on the MS Office Parameter of the Append Table function (which returns a Cluster constant), then double-clicked the edge of the Cluster (a Magic Trick I learned several years ago).

You'll notice that I do not Dispose Report -- I'm simulating a "crash" in the LabVIEW program after only 10 out of 10,000 iterations.  What happens?  The Excel file stays open, you can "manually" close it.  Note that if the Save process takes too much time, you can simply eliminate it (or, much better, put it, and the Dispose Report, outside the For loop when it exits).  As long as Excel (or the entire PC) doesn't crash, you can again "manually" save (but remember to save!!) and close Excel.

So here's the snippet.  This should solve your problem (mark this as a solution if you agree).

Bob Schor

[reesethelabviewguy]

Thanks so much for that! It got me really close to the solution. However, I just tried this set up to see if it would work. It goes through the first loop and then when it gets to the 2nd loop, it gives me this error coming out of the "save report to file.vi":

Error 5 occurred at Open/Create/Replace File in NI_Excel.lvclass:Save Report to File.vi->Power Cycle Test 3.0 Excel Save Testing.vi

Possible reason(s):

LabVIEW: File already open.
=========================
NI-488: Command requires GPIB Controller to be System Controller.

So it's not letting me save over the top of the already open and saved file. Does that make sense?

I attached my VI again.

Thanks for your help!

[Bob_Schor]

Reese,

     Go back and read all of the previous posts, and print them out, if you can.  In particular, look at the earlier posts that show much simpler (and thus better) ways of creating file names than the convoluted mess you keep flaunting.

     Now, this step is the most important.  Delete all copies of your earlier efforts!  This will force you to start over, and when you do, you can use the improved code that we've provided to you.  This will do several things (in addition to making us feel like you are listening to us and following our advice) -- by simplifying the code, you'll begin to see what is important and what is not, by learning more about how LabVIEW works you will begin earn the moniker "thelabviewguy", and by looking at the forest instead of the trees, you might begin to understand how to fix your problem (sometimes called the "aha!" Moment).

     Look at the earlier example I provided (notice how compact it is!).  It has four parts, proceeding from left to right:  Create an Output Report name, open Excel using a Template, generate data, write and save Excel using the Output name.

     Now look at my second snippet and ask, What's Different?  (Hint -- the key difference is not putting the Excel Save inside the loop).  Again, if you follow the steps in the first two paragraphs, particularly the "Delete and Start Over" step, duplicating my final snippet yourself, the aha! Moment should announce itself.

     Does this mean you can't [do the step that I left out]?  No, but you need to do it differently.  Can you see how?

Bob Schor

P.S. -- I'm not "trying to be mean".  I'm assuming that you are really interested in learning LabVIEW, and I'm happy to help you with this.  If you were here, we'd talk about it and I'd ask "embarrassing questions", but since you are not my student, I need to resort to "tricks" to get you to "do the right thing".

[reesethelabviewguy · Accepted solution]

Thanks for your help Bob. Next time, actually answer the question that was presented and dont dance around things that are already known..."(Hint -- the key difference is not putting the Excel Save inside the loop)." Hint, that's what the whole question was about. 

Anyway, for those of you that were stuck like me. I found my solution here: 

http://forums.ni.com/t5/LabVIEW/Why-can-I-only-save-one-time-with-report-gen-toolkit/td-p/883018

This basically just modifies the Save/Open/Replace vi so that it just saves and doesn't check to see if the vi is open. This is essentially executing the keyboard shortcut Ctrl+s so you can acheive saving in real time with the Excel file open. I'm not sure if this is an issue in later/early versions of Labview, but I know from personal experience it is an issue in Labview 8.6.

Hope that helps!

[RTSLVU]

---

@Bob_Schor wrote:

Reese,

     Go back and read all of the previous posts, and print them out, if you can.  In particular, look at the earlier posts that show much simpler (and thus better) ways of creating file names than the convoluted mess you keep flaunting.

---

Agreed Even my previous suggestion was more convoluted than it needs to be.

This is the proper way to build your path.