averaging excel data using labview

[dannyjhu]

I have about 500-700 microsoft excel files that I would like to extract the same 10 cells from each file,  paste the cell data into a new excel spreadsheet for later trend analysis.    I started looking into doing this within excel using visual basic script but then thought it might be easier to do using Labview read and write to excel file function.  Has anyone done anything like this and can offer any tips that might help?  If I direct the read from excel function to the host folder containing the excel spreadsheets, how do I get it to open each speadsheet once without issing any?  i'm assuming its not as easy as making file open path *.*.  

Thank you for the help.

[JarleEkanger]

keywords are:

File path control: browse for existing folders -> input to the "List Folder.vi"

List folder: get the "file names" output (1D array of strings). You might want to use the filtering input (eg "*.xls").

Wire folder path and 1D array of file names to a for loop indexing through the filenames. In each iteration, build a path from the folder path and the filename using "build path". 

Open the file, read your values, write them to another (already opened) excel file.

[nyc_(is_out_of_here)]

---

@dannyjhu wrote:

I have about 500-700 microsoft excel files that I would like to extract the same 10 cells from each file,  paste the cell data into a new excel spreadsheet for later trend analysis.    I started looking into doing this within excel using visual basic script but then thought it might be easier to do using Labview read and write to excel file function.  Has anyone done anything like this and can offer any tips that might help?  If I direct the read from excel function to the host folder containing the excel spreadsheets, how do I get it to open each speadsheet once without issing any?  i'm assuming its not as easy as making file open path *.*.  

Thank you for the help.

---

If these are true Excel files versus *.csv files, get familiar with ActiveX.

[RTSLVU]

Start with was JarleEkanger said but read each of your files into a 2D array (Use the Spreadsheet to array function)

Extract the cells (Index Array) you want from the array and write them to a new spreadsheet file (use the array to spreadsheet funnction)

Continue until all the files are read.

[Sam_Sharp]

Yeah, if the spreadsheets are actually Excel (.xlsx) versus a text file (e.g. CSV) then you'll need to use ActiveX or I think you can also use the report generation toolkit?

[RTSLVU]

I have found using ActiveX to automate Excel methods to be unreliable at best.

Even the NI examples may or  may not function properly on any given system.

I have spent several hours on the phone with NI support trying to figure out why their examples work fine on one machine and fail on another with exactly the same OS and software versions, and found no solution.

In the end we abandoned ActiveX all together, I suggest one of the third party add-ons like XLR8 if you need to do a lot of work with Excel files (XLS, XLSX)

[Bob_Schor]

The Report Generation Toolkit make working with Excel a breeze.  You do not need to use ActiveX for most things.  Although NI treats the Toolkit as for "Report Generation" (i.e. "write-only"), there is very little problem using it to read, modify, and write Excel (.xls or .xlsx) file.

Jarle's Algorithm should work.  You can use LabVIEW's file-handling functions to find the Excel files for you (by doing directory listings).

Open one Excel file for your output file (keep its Report "wire" separate).  Now start processing the Excel files you identified -- in a For loop, open a file with New Report (specifying the file you want to read as the "template"), extract the data, close the file, then write the data to the output Excel file.  Repeat until done, then close the output file.

If you do not have the Report Generation Toolkit, I'm not sure I would advocate doing this in LabVIEW ... ActiveX is a bit tricky (and I think Microsoft has abandoned it ...).

BS

[dannyjhu]

Thank you to everyone for your help.  I do have the report generation toolkit but have never used it.  Now that I have some good suggestions for how to proceed, i'll try and get started on some VI's over the weekend.

Thanks again for the help.

[Bob_Schor]

Take a look at a Revised Demo I posted here.  It should help you get started quickly using the Report Generator Toolkit to link LabVIEW and Excel.

Bob Schor