Excel to front panel image

[dattu78]

HI,

I have a formatted range of cells in excel from A1 to X45. The formatting has headers that are fixed values. Each time, i run my vi, only the data part (cells A2 to X45) of the table gets updated with various measured values. 

How can i save this Table after every updation as an image?

I tried using Active X control on front panel and create from object, link to file and pointed the file. But it doesnt update the table on front panel everytime.

Please let me know if there is any other suggestion.

Thanks. 

[Bob_Schor]

For detailed help regarding an Excel Workbook we haven't seen, and unknown LabVIEW code that interacts with this Excel Workbook in ways we can't examine or test, the best advice I can think to give you is Post your Code and your Excel Workbook so we can understand what you are trying to do, and can suggest how you can accomplish your goal.  Note that we need to have working LabVIEW code -- if you send us screen-shots or "pictures" of code, that won't be helpful to us, as we'd have to spend our time, unpaid, trying to duplicate the code in your pictures (I, for one, won't do this!).

Bob Schor

[dattu78]

Hi Bob,

Thanks for your response.

Attached are two zip files that has two different methods of implementing the "Goal".

Goal is, to create a "test report.xls" file from the template "Test_report_template.xltx" and after saving the file, display the entire table in an active X container on the front panel.

Out of the two below implementations, NISupport.7z is the method i followed with MS Office Report express vi. In this method, as u can see there is an unwanted data being inserted in column "D". I am not sure why. Do you know how i can get over with it?

The second zip file "RGT_Excel_specific_method.7z" file tried to achieve the same using RGT kit excel specific functions. I am getting the below error and nothing happens.

"

Exception occured in Microsoft Excel: You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name, or close the other workbook or add-in before saving.Help Path is xlmain11.chm and context 0 in NI_ReportGenerationToolkit.lvlib:Excel_Save_Workbook.vi->NI_Excel.lvclass:Save Report to File.vi->RGT_Excel_specific_method.vi

"

I am assuming that i am accessing the same file in Active X container that the Excel specific RGT kit is trying to update and hence its throwing em an exception. How can i get over with it?

Appreciate your help.

Thanks.

[Bob_Schor]

Hello, Dattu.  Thank you for attaching your VIs, especially your Template.  I apologize that I was busy during the day and didn't see your response until I checked the Forum just now.  I haven't (yet) looked at your code (and probably won't get to it until tomorrow morning), but there are some questions that occur to me right away:

1. I notice that the Template has "blanks to fill in" in rows 2, 3, and 4 (though I'm unsure which column you are using).  Do you intend to fill these in as part of your LabVIEW program?
2. A9:C52 are "pre-filled" with what I assume are constants.  Did you want to use these values to drive your LabVIEW routine, generating data that you would put in D9:L52?
3. What about Rows 52-57?  Are any of the cells being filled?  I'm especially interested in Row 57, which I hope you don't plan to "pre-answer" as "Yes" (Meets Specification).

The Report Generation Toolkit uses the term "Template" in the Create Report VI.  This is not the same meaning of "Template" that Excel uses for .xltx files.  In Excel, a "Template" (.xltx) file creates a corresponding .xlsx file with a "similar" name, with any cells already filled in and formatted "copied" (or "initialized") in LabVIEW.  What the RGT means by a "Template" is simply an Excel file that is open and read.  Sounds like the same thing, right?  Not quite -- the name means nothing to LabVIEW until you "Save Report to File", in which case only the File Name that you specify for that latter VI gets saved to Excel.

For example, you have a Template, A.xltx, and you Create Report specifying this as the Template File.  Doing this will cause Excel to open a file called A1.xlsx (at least this is what happens on my PC with Office 365).  Say you use the RGT to modify the file, then Save Report to File, specifying the name New A.xlsx.  When you finish, you'll have the original Template file, the saved New A.xlsx, and the "temporary" file A1.xlsx will be discarded.

On the other hand, suppose you have a copy of the Template called A.xlsx, i.e. not a Template file, but an ordinary Excel file, and you Create Report specifying A.xlsx as the RGT "Template".  Excel will open this file, and call it A.xlsx.  You modify it, and Save Report to File -- whatever name you use for this latter function will be the saved File Name.

In order to "fill in the Template" properly, you need to know where to "fill in the Blanks".  There are two ways to do this (and I've used them both).  One is to read in the Template, "parse" it by locating the Blanks (in relationship to row and column labels), and from this parsing, figure out where the relevant data goes.  For certain kinds of Excel Worksheet, this approach works well, but it is a bit tricky to program.

The other way, exemplified in the Revised Example that I suggested to you, is to do away with a separate Template and have the RGT not only "fill in the Blanks", but also "Create the Template" (i.e. write the Row and Column headers as appropriate.  This has the advantage of flexibility and letting you, for example, change the number of "Sample Rows" on-the-fly.

I'll be back in the morning, and will take a look at your code and see what else I can suggest.

Bob Schor

[dattu78]

Bob,

Thanks again for your response.

"I notice that the Template has "blanks to fill in" in rows 2, 3, and 4 (though I'm unsure which column you are using).  Do you intend to fill these in as part of your LabVIEW program?"

YES.  I will be filling these blanks also with data from VI.

"

A9:C52 are "pre-filled" with what I assume are constants.  Did you want to use these values to drive your LabVIEW routine, generating data that you would put in D9:L52?

"

YES. Because these constants would vary for dfrnt templates.

"

What about Rows 52-57?  Are any of the cells being filled?  I'm especially interested in Row 57, which I hope you don't plan to "pre-answer" as "Yes" (Meets Specification).

"

These cells have formulae pre-filled in them. Row 57 has an IF condition formula, based on the result of calculation, it will tell if the parameter will meet the spec or not.

In my case, the blanks are fixed and i can direct the labview program to fill the data into these blanks by named/cell reference. 

Be'se of this, i was trying to use the example program as suggested in "excel report generate from template". But this has only caused more confusion than clarity.

My goal is to create an image of the excel Table after filling it with appropriate data. If this "Active X container" method doesnt work, I am thinking of writing an excel Macro in the "saved excel file (not the template)" each time the data is saved using labview and run it through labview. 

PLease let em know u thoughts on this. 

Thanks,

DAttu.

[Bob_Schor]

Greetings, DAttu.  Thank you for correcting my capitalization of your name.  Those of us who write "LabVIEW" know how important capitalization can be!

As it happens, I recently wrote a VI that ran some tests and wrote out the results in an Excel Workbook so that the Engineer who was testing the component would have the data necessary to know if it was behaving properly.  This sounds not too dissimilar to the situation you describe.  My particular Report had fewer sections than yours, but the principles are largely the same.

The general idea was that LabVIEW collected and organized all of the data that went into the Report, including

• Identifying Header Information (Manufacturer, Serial Number, Test Date, etc.)
• Test conditions (an Array of Specifications, ITU Lambda, IT U Freq)
• A 2D Array of Test Results in response to the Array of Test Conditions
• Analysis of these Data resulting in Conclusions (Minimum, Maximum, Insertion Loss, Meets Specs)

This is clearly a multi-step process.  LabVIEW has a variety of Design Patterns (including various implementations that include State Machines and Message Handlers) to help with this.  Equally important is the need to keep your code "manageable and understandable" -- one very useful technique is to "encapsulate the details" in (often many, and nested) sub-VI, each of which carries out a specific (named) function, and each of which is identified with an Icon that tells you just what it does.

Here's a Test routine I wrote that tests two Report Generators for a particular Test of a circuit Board.  We have 16 terminals that can be connected to 16 voltage and 16 "current-limiting" sources and want to measure 4 parameters (two currents, two voltages) for each of the 16x16 combinations of "A" and "B" settings.  Our Header consists of the Date, Start Time, End Time, Tester, and "Build" (version of the Test routine), plus information on the Board and Test settings.  We take one reading "Before" doing the Test, then for each "A" setting (A01 to A16), we do a test for each B setting, and when all 256 are done, we do one more "After" Test (the Before and After are with the Board "at rest").  Here is the code that generates this test:

Note that this is a LabVIEW Snippet, meaning you can drag it to a 2019 blank VI and it should try to recreate the underlying LabVIEW code.  Because there are sub-VIs that are not included, however, it won't really run (these VIs will show icons with big Question Marks), but you can see the nature of the code.  The three While loops generate Before, During, and After data (Before and After are a single row-of-4 points with unique values to ensure that the data are written correctly, while the During data are generated by a 16 x 16 For loop surrounding an inner "row-of-4" generator, again with sufficient "uniqueness" to the data to be sure the Report is correct).

The code shown below handles the "steps" of the Report Generation.  Start A-B Header saves the date, asks for the initials of the Tester, retrieves (from Subversion) the Build number of the Test, then asks a second question (output in the second Cluster wire) about the Board data.  Just before generating the data, LabVIEW fills in the Start Time.  I put a 3-second "wait" to simulate "running the test", then finish the Header by getting the Stop Time.  Finally, I combine the Header and the simulated Data into a Report.  The second Report uses the same Operator/Board data, but a different set of Test Data (produced by routine Test DrVCs Data) and writes a second Report.

The Report is written by the fourth VI, Report A-B Relays, shown here in Snippet form.

 

The inputs are the Data (coming in from the Cluster generated by the Test routine shown above) and the two sets of Identier data, coming into Variant inputs.  Why Variants?  I wrote a routine, "Save Header Info", that expects these Identifiers to be a Cluster consisting of Header Elements (Date, Time, Tester, etc.) whose Element Name would go in Identifier Column 1 and whose Value would go in Identifier Column 2.  Save Header Information picks apart the Cluster and separates each element into Name and Value to make the Header.  Save Header Info passes out the Next (BL) and Next (TR) Excel Locations from Excel Easy Table, and I use a little VI "Skip N Rows" (default value 1) to skip a row before putting the CB Info identifier information on the Worksheet.  Now I'm ready to write out the Cluster with the data.  I move over a Column (using Skip N Cols) and first write the Header for the Data Colums (in Report A-B Labels), then I write all of the data.

The first VI in this chain determines where I want to save the Report, and what name I want for the Report.  This is passed to "Save Report to File" and then I close Excel with "Dispose Report".

The process is very fast.  LabVIEW does all of the "heavy lifting", gathering the data, querying the Tester for Test-specific information (like Board ID and Tester name).  In this example, I'm not doing post-test computations, but if I needed to, I could do it after collecting the data, and simply append another series of rows below the main data rows output by Report A-B Data (perhaps using Skip N Rows to leave some blank space).

In principle, the RGT would allow you to go into an existing Template, figure out where the data should go, and "make it fit", but (having done this once) this is a lot of work!

Bob Schor

P.S. -- In creating my Excel report, I did not save it as an .xlsx, but as a .csv (so that anyone could read it without needing Excel).  Unfortunately, that led the Forum's engine to reject the Attachment.  I've accordingly "buried" it into a Zip file -- simply unzip and you'll have the .csv.

[dattu78]

Bob,

Thanks for your response.

My name is Dattu. No emphasis on capital A. In fact it was a typo :). No worries there.

I tried to drag and drop your snippet code both onto front panel and block diagram but it only gives a link instead of generating the code.

Please let me know if there is any other way that i can copy the code?

Also, if i use an active X container on the front panel, can i control when to get the object to display into the container? For example, in the case of copying the Table

 from the test report, can i ask it to fetch the table after say 20 seconds, so that meanwhile all other updating the table process is done?

I tried copying and running a macro in the test report excel. The macro copies a selected range in the excel to image. Everything was going good until this point. Later, i used a .net picture box to display the image that the macro saved on my disk. As long as the program is running, the picture box showed the image but the picture disappears once the program is finished. So back to square one!!!

Please let me know.

Thanks,

Dattu.

[Bob_Schor]

Dattu,

     My apologies.  There is a problem with Snippets if you drag the images from the Forum.  Look at the links on your Block Diagrams -- the first part is the actual link, and then there's some additional stuff that starts with /i.mage-size.  If you throw away the stuff after /image-size, you'd get the actual Snippet.

     However, as I explained, I use a lot of sub-VIs (and TypeDefs, such as Type A-B V-I, which you can probably guess is a cluster of a 1D array of Dbl (Before), a 3D array of Dbl (During), and another 1D array of Dbl (After)).  In the Snippet, since there is no code referent for the Type Defs or sub-VIs, they'll appaer as empty squares with a big "?" in the middle, not as helpful (I'd think) as the actual "picture" that I posted.

     I was trying to illustrate a process -- a series of simple steps that "do one thing".  Look at the Report (contained in the ZIP file).  The first three VIs are concerned with the Report "Information", the two clusters of information on the left that include Date and Time, and information about the Board under test.  Remember, this is a Test routine to simulate "real data" (generated by the For Loops at the top).  The first VI gets data from the User, the second is called just before testing starts to get the time that Testing started, I wait 3 seconds to simulate the test taking place, then update the time Testing stopped.  These two "Clusters of information", along with the Data Cluster, go into the fourth VI that actually writes the report, and is shown on the lower Snippet.

     Again, more sub-VIs to "do one thing".  I have a VI that determines where the Report will be saved ("Report File Path") -- it takes a parameter that has the name of the Report and adds a non-repeating suffix (here "(001)" ) to it.  This will eventually be used in Save Report to File.  I put it first so I could send the Path Wire below all the other sub-VIs and reduce wire congestion (I like to keep my wires straight and "untangled" as much as possible).  As I mentioned earlier, Save Header Info writes the data in the clusters holding the data in Columns A and B of the Report, with a little sub-VI in between to maintain alignment and spacing of the Excel Easy Table arrays of data.  The final two of my "Blue VIs" take the Data Cluster and write the two-column Row Headers (I think they are Excel Columns D and E) and then the 258 rows (with a header Row) of the A-B Data contained in the big Data cluster.

     I will admit there's a little bit of "magic" in the routine Save Header Info.  Recall this does the two-column header information in Excel Columns A and B, with the Header values contained in a Cluster.  The Cluster has elements with names like "Date" or "Tester" holding String or Numeric data.  What I want to do is to use the Cluster Element name in Column A (e.g. "Date"), and Element value in Column B (e.g. "26 Mar 2020").  The Header Info Control of this VI is of type Variant, so it can accept any Cluster (or anything else, for that matter, but I'm only allowing Clusters).  Inside looks like this (another Snippet, but I'm attaching the .png, so you can drag that to a blank Block Diagram):

I "know" that Header Info is a Cluster, so I don't really need the first VI nor the Case Statement, but I have a bad habit of "wiring backwards" which causes me to make "hard-to-find wiring errors", which this construct catches for me.  Ignore it, just concentrate on the code inside the Case Statement.  The first two "green VIs" are from the OpenG Toolkit, available on the LabVIEW Tools Network.  These two VIs work with Variants.  The first one returns an array of the Cluster Element names, exactly what is needed for the Row Labels.  For the rest of the code, read the explanation shown on the Block Diagram.  We passed the Report Object in, so we simply write this Cluster as a one-column row of Data with Data Labels, and pass out where the "next" set of data could go.

You keep mentioning ActiveX and .NET, and difficulties getting these to work for you.  However, you've (so far) not posted your own code, so I don't really understand what you are trying to do, and so can't really tell you how to accomplish your goal.  It's really time for you to post your code.  Note that for it to be of much use/interest, it really needs to be code, not Snippets, with the best being to compress the folder containing the entire Project and attach the resulting .ZIP file.

Bob Schor

[dattu78]

Bob,

Still no success in extracting the code from the snippet:

[Bob_Schor · Accepted solution]

Try one more time using the following steps (which I just tested, and it worked for me):

1. Click on the link and download it to your "Downloads" folder (I'm assuming Windows 10).  Note I'm also assuming 32-bit LabVIEW 2019 (or 2019 SP1).
2. Open LabVIEW 2019.  Type ^N to open a blank VI.  Type ^E to get to the (empty) Block Diagram.
3. Open Windows File Explorer and arrange things so you can see both the blank Block Diagram and the contents of the Download Folder.
4. Click on the downloaded "Save Header Info.png" file (don't open it!) and drag it onto the Block Diagram.
5. Wait 2-3 seconds -- the Block Diagram should appear, "by magic".

Bob Schor