LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a new excel using an existing .xlsx template with each run/instance

The issue I am facing with this is that there is no new file created in the specified folder. Even the existing file does not have data added to it (though I have managed to do that separately, but that's not my desired outcome right now).

 

I want to use an existing .xlsx template, write data into its Sheet2 (to link to Sheet1) and save it as a new .xlsx file with a different filename (using datetime to differentiate). I'm using LabVIEW 2022 (32-bit). Apologies for any obvious mistakes, I'm fairly new to this software.

 

LVSnippet.jpg

 

0 Kudos
Message 1 of 9
(1,586 Views)

To build a Path for a file, you should use the Build Path function.  I really dislike using a TimeStamp as a file name:  it gives you no clue what data the file contains, it is hard to sort, confusing to the eye to read, etc. 

 

To keep my files-of-the-same-type-of-information straight, I use the "Incrementing Suffix" function, which has (for my use) one tiny flaw -- if there are no files with the same name, it creates a file without a suffix.  I fix this by "wrapping" my own code around this function -- I create the file, and see if the "created name" is the same as the "starting name".  If so, I create it again (causing a name with the (001) sufffix to be generated) and delete the suffix-less name.  I don't remember, but you should probably close the un-suffixed file before deleting it.

Here's a Snippet:

Build Path + Incrementing Suffix.png

Bob Schor

0 Kudos
Message 2 of 9
(1,547 Views)

@ashfeq :

Are you sure LV has the correct write privileges in the target folder? No error reported?


I replicated your code on my PC and the new file gets created correctly...


(yes, there are 'more elegant' ways of building a filepath 😉 )

0 Kudos
Message 3 of 9
(1,535 Views)

Things to check:

1. It automatic error handling turned on?  If not, then you will not see the error if the template file cannot be opened

2. Does the template document have a sheet 2?  If not, no data will be written

3. Single-step the code and see if your template has been opened, Sheet selected and data wriitten

4. Replace the hard-coded template name with a file path control and browse for the file.  This insures the path is correct.

5. Double-click the NI Excel VIs before running the program to see if the control values make sense

 

You named the image LVsnippet, but did not use the Edit -> Create VI Snippet to create it....

Michael Munroe, CLD, CTD, MCP
Automate 1M+ VI Search, Sort and Edit operations with Property Inspector 5.0, now with a new Interactive Window Manager!
Now supports full project automation using one-click custom macros or CLI.
0 Kudos
Message 4 of 9
(1,507 Views)

Thanks to all who replied,

 

I have managed to make some progress. Firstly, with the new changes I've made in reference to all your suggestions, I was able to create new .xlsx files with incrementing suffixes and removed the datetime naming as Bob suggested.

 

However, now, I am unable to open the excel file as it gives the error as attached in the image below. I don't think it is a problem with the permission for writing to the directory as I am able to create the files now, right?

 

Also, for Michael, I tried disabling the program from the 'Save file to report' till 'Dispose report' VIs. What it did was to write the array into Sheet2 of the original template excel 'LabViewTest'. Thanks for the help in creating a snippet! I've attached a proper one this time.

 

cd81b511-6c43-4eb1-9c96-f993f1e5bff2.png

 

testoutput.jpg

testoutput1.jpg

0 Kudos
Message 5 of 9
(1,477 Views)

So this is where I'm at with the problem now, using elimination method to rule out some of the potential issues. 

 

I tried using a very basic program to do what I want, but using Word doc instead of Excel (snippet attached below).

 

And it works. It creates a file, and uses the template provided and opens perfectly. 

 

So what I tried to do was, the same program I used for the word doc, I replaced certain VIs to adapt to the excel counterpart and... still nothing. (I used the datetime naming again just for simplicity's sake, even for the word doc). No new file gets created. It's definitely not a permission problem, if I'm able to generate new word docs.

 

I've attached the snippet for both. TIA!

Download All
0 Kudos
Message 6 of 9
(1,462 Views)

How do you expect us to test this without a Template?  I notice you are using an .xltx file, rather than the .xlsx format that LabVIEW knows and understands.  I strongly suspect that NI's Report Generation Toolkit might not support this "Excel Template" file.  Anyway, you should attach your Template File so that we can properly test (and understand, and potentially "fix") your code.

 

Bob Schor

 

P.S. -- I'll point out to you that your Post says "using an existing .xlsx template", which I've tested and use all the time, but your recently-posted code does not use an .xlsx template ...

0 Kudos
Message 7 of 9
(1,451 Views)

You're absolutely right Bob. Apologies for the difference in the title and what I posted. I've actually been trying every different possible combination to try and get the program to run, one of them being -- trying an actual excel template (.xltx) instead of a workbook (.xlsx). I've already tried all the methods suggested even using .xlsx. Feel free to replace the template with the workbook, both of which I'll provide below. It's actually just a mockup of a very basic and simple template linking 10 cells from Sheet2 to Sheet1. My thought process is that, if it works on this basic excel, it should work for future, more complex ones.

 

Thanks for pointing out, Bob!

Download All
0 Kudos
Message 8 of 9
(1,432 Views)

So as it happens, I'm currently working on a LabVIEW program that uses the RGT to take a Delimited Spreadsheet file created from another LabVIEW program doing behavioral testing on mice (training them to lick from a tiny water spout when a rough surface comes within "whisker range", but to not lick when a smooth surface is presented.  I've frequently used my own File Extensions to "label" data files -- these files are called ".evnt" files, and have an "Event" column (an Enum with values such as "Go", "Lick", "Reward" Tem) and "Event ms" (an I32 that gives the number of milliseconds since the beginning of the Session that, for example, the "Go", rough, target moves into whisker range, "Lick" is when the mouse licks, and "Reward" is when the drop-of-water reward is presented).

 

My Excel Template (called Excel Template.xlsx, located in a Project sub-Folder called "Templates") has a

number of WorkSheets, one called "Events" (where I store the "raw data" saved in the Events.evnt as a LabVIEW Delimited Spreadsheet text file) will be filled with the "Event" and "Event ms" data.  Several other WorkSheets will have some data from the 100-200 Trials in a session (one Trial per line), including a Histogram that starts in Column F, and a final WorkSheet I call Params, where I save some of the Parameters that this programs uses to analyze the Events WorkSheet and build the outputs that go into the Trials WorkSheets.

 

Here are several thousand words:

XL Template 1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This is the first WorkSheet, designed to get the raw Events.evnt data, where the Event column holds a string representing an Enum, and Event ms gets a string representing of an I32 representing the time of the Event, in milliseconds.  Note also the WorkSheet Tabs.

XL Template 2.pngXL Template 3.png

 

 

 

 

 

 

 

 

 

 

 

 

The All Trials WorkSheet will hold several hundred Trials in the Session, one Trial per row, generated by LabVIEW software.  Trial and Type come from parsing the Events WorkSheet and finding where Trials start.  The start of a Trial is marked by an Event ("Go" or "NoGo"), and we start our analysis starting 3 seconds before the Trial starts (which is in the "Event ms" column) and analyze until 2 seconds after the Trial starts.  During these 5 seconds, we build a Histogram of Lick occurance ("Lick" is just another Event, and has an associated "Event ms".  There are other columns such as "Decision" (such things as "Hit", "Miss", "False Alarm") and Latency (time of first Lick after the Trial starts).

 

The last WorkSheet picture shows part of the Parameters WorkSheet.  Notice it contains (user-selectable) parameters that determine the Bin Width (in milliseconds) of the Histogram of Licks that go on the WorkSheet, where the binning starts relative to Start of Trial (3000 ms = 3 seconds, as noted above), and when the Histogram ends following the Start of Trial.  The last two parameters show where on the WorkSheet the header for the Histogram should go, and where the Histogram, itself, starts (Cells F1 and F2, which are currently blank, but will be filled in at Run Time).

 

And here is a (very much non-working "picture" of the code at the present time, not all of the the sub-VIs are finished, all the bugs aren't yet out, but it "mostly works").  It is "just a picture", but you can recognize RGT functions, including the function that lets me select WorkSheets for reading and writing, and some utilities I wrote to translate "Excel Addressing (such as A2, Column A, row 2, to Excel Row/Column values, where A2 becomes (0, 1), Column 0, Row).  Boy, I hope I got that right ...

XL Template Stuff.png"

I'm putting out this "Work in Progress" to show one way to take advantage of the RGT and using Excel Templates (where "Template" means an ordinary Excel File that you modify at run time and save under a different WorkBook name (I'm using the name of the original Event.evnt file, replacing the ".evnt" extension with the standard ".xlsx" extension by using the OpenG "replace Extension" function, shown in OpenG Light Green).

 

Bob Schor

0 Kudos
Message 9 of 9
(1,398 Views)