reading multiple excel workbooks

[MechE punk]

Hi all,

LabView noobie here.  I want to apologize in advance if I have posted this in the wrong spot or if I have not properly searched the forum for existing threads on this issue.  My initial search returned tips with excel but nothing I found immediately useful. 

I am an engineer who uses labview to run life cycle testing on our products.  I did not create the current program we are using, I just inhereted it when the guy who did got let go 🙂

We currently measure: time in one column, then amps, volts, and temp in the following columns for each cycle (up or down) and save the runs as seperate excel files.  We test our products anywere from 200 to 10000 cycles and record every 10 and 100 cycles (runs) respectively.  On average we record over 1000 rows of data for each of the 4 columns for one run.  Basically, we record approximately 4000 bits of raw data (in 4000 cells) in each seperate spreadsheet and have upto 500 spreadsheets for one complete test. 

Currently, I take a sample of X different spreadsheets and copy/paste the raw data measured by labview into an existing excel template with X different sheets that has been created to perform the calcualtions I require. 

What I would like to do is make a VI that can open X different spreadsheets, chosen at my discression, read/copy/paste them into the existing template and save that template with a unique file name based off of some constants that can be entered by the user.  For example, If I want to choose the following "runs" to analyze: 1000, 2000, 3000, 4000, and 5000 I would either be prompted to choose those files specifically or (more preferably) I could just type in those numbers into the VI and it would go to those runs and open/copy/paste them into the template.  Ultimately, I am trying to make a button on the front panel that when you press it, it prompts the user for either the files or "runs" that you want to compare and saves them to a workbook that can be viewed as a report. 

Any and all ideas on getting started would be appreciated. 

[cancancanopen]

Hi MechE punk

I understood some of the things which you want to do,

Labview report generation is good option, understand that you have many different spreadsheet which contain huge data and you need to dump this data to the existig available template.

My question is would you like to store the data in single template or many such templates?

You can try this,

1. Goto Report generation select new report , provide a path where the spreadsheet is present,.use the excel get data VI, this VI is polymorphic, select appropriate option by performing right click on VI and select type option.

2. Use the Excel easy table VI to dump all the data in the current sheet of your template.

3. Put this in a loop where the loop count will be no.of files in your folder.

4. You can save report each time data is dumped.

5. You can save and dispose report to the original spreadsheet file and then open the other spreadsheet.

Hope my understanding of your problem is clear,

Thanks

[MechE punk]

I would like to read/copy from multiple excel files and paste them into a single template with the same amount of sheets as there are files I am reading from.  So if I am reading from 10 files I would have 10 sheets in the template so that all of the data from each file can be pasted onto its corresponding sheet in the template.

I have some questions that correspond to the steps you listed (1-5):

1.  on the "New Report" VI can the path I provide just be the folder where all of these raw data files are stored and can I make it prompt me for which specific files to choose (or how do I tell it which ones to pull); or am I providing a path to the template? 

2.  Can the "Excel Easy Table" VI dump the seperate files containing my raw data in the same location but on different sheets of my template?  Also, where is this VI located.

3.  I do not want it to generate a report for every file that we save.  I want to take a sample size of all the runs for a test and compare them to each other to analyze the current draw and time, etc to check for overall performance and degredation.  So it will be one report based on let's say 10 of the 500 files for each test.  If I only wanted it to read from the cycles I stated earlier (1000,2000,3000,4000,5000) how could a loop handle picking those specific runs opposed to just going through all of them?

4.  It would be fine if it saved it after each time data is dumped but I would want 1 file name for all of the sample data that is being saved/dumped on the various sheets of the template (report).

5.  When you say "save and dispose to the original spreadsheet" I think you are thinking that I want to just save one file of data on the template, save/close it ,and open a fresh template in order to repeat the process with a different raw data file.  What I would like to do is open a raw data file, dump that data into sheet 1 of the template, open another data file, dump that data into sheet 2 of the template, etc until I have 10 sheets worth of data on my template.  Afterwards the template can be saved as one single report that can be viewed to determine if a part passes life cycle testing.

I think we are more or less on the same page but you weren't sure if I was using multiple templates and I think you wrote your response under the impression that I was.  I am using one template that needs to receive data from more than one file.  I hope this helps clarify the issue a little more.

[cancancanopen]

Hello,

I got your point, You have say 10 different excel files and you want to dump it to one single template file.

Answering your questions in order,

1. According to me we cannot give folder option and ask to open a dialog, New report searches for report file specific excel file or word file or html. If you have a loop program will take file one by one {flow you can decide in you logic}.

You need to give path to the template {i dont know whether template file is .xls or .xlt.... I haven't tried with .xlt file}, so you will be using 2 create new report VI, one for opening all the 10 or 100 spreadsheets, and other for the template.

2 . I assume your spreadsheets where all the raw data are present are in "SHEET 1" of the excel file, Easy excel table VI collects all the data from SHEET 1 and dumps on the sheet in the excel file, you can programatically control in which sheet you want to dump the data, there is EXCEL GET WORKSHEET VI which asks user which is the current worksheet you want to work on. So every time you get the raw data assign the worksheet for the template file and dump the data onto that worksheet.

3. I am confused about this, can you please elaborate,

4&5 ...... I meant when you read the raw data from excel file , once data reading is done save that file and dispose it as it is no more required, BUT  for the template file you use only save option, once all data are dumped you can dispose that template report.

Hope you find this usefull.

[cancancanopen]

First of all do you have Report generation toolkit for MS office installed, If yes then you can find all the VI's which I wrote in the excel directory, image attached for your reference.

[MechE punk]

1.  How can I use a single "New Report" VI to open all of the raw data spreadsheets?  It does not appear to be able to accept more than one input to be wired in.

 2.  Your assumption is correct.  Is "Excel Get Worksheet" part of the Report Generator Toolkit for MS Office?  I don't have this and am not sure if we will be purchasing this add-on.  Does this mean I will have to think of a different approach then what we have been discussing?

3.  I think you answered this in 4&5

Thank you cancan.

[cancancanopen]

Oho ho ho.... ok thats time waste, i should have asked this question much earlier, other option is use to Active X,

Goto front panel goto Refnum --> Select Automation Refnum and browse for the Excel workbook, work sheet and in the block diagram goto Connectivity ->active x

use automation open and property node and invoke node to achieve the task,

dude i felt this as tedious job, report generation addon makes life simpler.

Using active X try getting information here http://forums.ni.com/ni/board/message?board.id=BreakPoint&message.id=2396

[MechE punk]

Damn, that was a waste.  Oh well!  Guess I'll start playing with it now to see what I can get out of using ActiveX and look into getting the add-on when I get back from vacation.  Thanks for the help man.

[cancancanopen]

Welcome Sir, hope u get ur solution ASAP

[PJajith]

Hi..

Can you please help me to get the Report generation toolkit for MS office?

Regards

Ajith