Create new Excel workbook

[Gregory]

Empty path constant

[hiNI]

Thank You!  Got it to work now.  

Not sure if I should make another post, but how to automatically save the file without the program waiting on you to tell Excel to "save" file?  If the file does exist, and I write to it, I have to go and find the opened workbook and select "save" before the program can continue.  Can you automatically write, save then close the excel file?

Thanks,

hiNi.

[Gregory]

Yes, that is exactly what happens in your "Save Data" case.

[hiNI]

Hello,  when you run the program a second time (that is when the file already exists), the program waits until you acknowledge the "replace file" in the opened excel file.  Is there an automatic feature that will allow you to just replace the file if it exists and not wait until you hit the replace button in excel?

Thanks!

[Gregory]

Well when you create the report you have the option to show warnings, but you had that unwired so it should not be showing you warnings. If you run this code below do you see the same behavior? Select a file name for the constant on the left, change the new sheet name after each run and run it a couple of times.

[Bob_Schor]

I confess to being a little confused.  I looked (probably too briefly) at this thread, and thought the question was how to start a new Workbook (if one didn't exist) and then repetitively add data on successive calls.  I cooked up a little demo similar to gregoryj's, but then I saw the Title, read the first post, and am now not sure I understand the problem.

My routine, which I'll post if the Original Poster thinks it will be helpful, does the following:

1. If the Workbook Demo.xlsx exists, it is deleted (to "start fresh").  It also initializes the file name to <nothing> (in a Shift Register) and sets the initial starting Excel address to 0, 0 (A1) (in another Shift Register).
2. In a For Loop, 10 times, it does the following:
   1. Opens Excel with the (saved or initial) Filename, minimized (who wants to watch Excel?).
   2. Uses Format into String to make a column header from the (incremented) loop index, "Column 1".
   3. Passes this string through Build Array twice to make a 2D 1x1 Array, as Easy Table needs 2D arrays.
   4. Uses Easy Table to write the 2D String Array, using the Starting Excel Address from the Shift Register.  The output Next Cell Top Right goes back to the Shift Register to position us for the next loop and next Column.  Note I could have written a Column Header, but I wanted to illustrate how you can write multiple things (here it was only a single cell, but it could have been more) at a time.
   5. Generate a 10-element array using the Outer For Loop Index * 10 (so 0, 10, 20, ...) and numbers 1..10 (so I get 1, 2, 3 for the first loop, 11, 12, 13 for the second).  This is a 1D (row) array, which I put through Build Array to make 2D 1x10 (1 row of 10 columns), then transpose it to make a 10x1 Column array.
   6. I wire this to a second Easy Table, wiring the Start from the earlier function's Next Cell Bottom Left, i.e. below the Column Header in Step 4.
   7. I Save Report to File using the name Demo.xlsx mentioned in the first step, and wire this file name to the File Name Shift Register for the next time through the loop.
   8. I Dispose Report to close Excel.  For safety (because I've seen funny behavior in the past), I put a 100 millisecond wait after Save Report and another one after Dispose Report.

This generates an Excel Workbook with 10 columns, labeled Column 1, Column 2, etc, with the numbers 1 to 100 arranged 10 at a time, going down by columns.

Oh, what the heck -- here it is, even if it does not answer the right question (I apologize for that) ...  Oops, can't paste the image (most of it doesn't show), so I'll just attach it as a Snippet.

Bob Schor

[hiNI]

In response to gregoryj's question....

I do see the same behavior if I run the program a second time.  On the first run, if the file does not exist, the program runs and then ends.  On the second run (after changing the sheet name), the program hangs and waits until I click yes to replace excel file.

Thanks,

hiNi.

[Gregory]

Interesting, on mine I do not get a prompt, which is what I would expect since the Save VI has prompt to replace set to false.

[hiNI]

I think I see where the problem is.  I had other workbooks open.  If excel if fully closed down, I do not have to select "yes" to replace.  If excel is open, I have to select "yes".

In summary, even though the actual file is not open, once Excel is open, I have to select "yes" for the program to continue running.  Is this the inherent operation of this code?

Thanks,

hiNi

[TimothyDug]

hiNI,

I am wondering if you could use the Write To Measurement File express VI. Forgive me if this is less complex than what you need, but when you create it, you are given the option to select what kind of File Format to export to (.xlsx).

You can also set it to "Save to one file" and then set its behavior if the file already exists. 

Then, you can set the "If a file already exists" behavior to "Use next available filename". This will, for example, make test1, test2, test3, etc. if your path is "C:\Users\Admin\Desktop\test.xlsx".If you instead want overwrite behavior, you can set that.

Let me know if this is somehow too simplistic for your application. Hopefully this is some help!