reading excel file dynamically

[b9]

bob, basically like i mentioned in my previous message, using the template i attached, my data will be recorded and updated on the next column as new readings are taken, so i want to be able to read those data as i leave my excel file open as the data is updated while excel remains open. so for me to read the data, i initially relied on 'read from spreadsheet' vi to read off the data which does the job but my problem is it only reads the latest 'saved' file. so i was wondering if it was possible to read it off dynamically without relying on saving->close->open-> record process where i need to constantly click which is a hassle. i hope this clears any confusion i made as i have forgotten to mention my main issue. my apologies.

[Bob_Schor]

This is why it is so vital to carefully understand and describe what you are trying to do.

From the appearance of your Workbook, I'm guessing the following:

• You are taking periodic spectral readings where you get discrete values from 360nm to 780nm every "so often" (having a sampling time would be useful to know).
• As each reading (array of 421 numbers) comes in, you want to add these data as a column to an Excel Worksheet (initially blank, I presume).
• While the data are coming into LabVIEW and being written into Excel, you want to be able to view the Excel Worksheet (I'm not sure if this is true or not -- you tell me).
• You may or may not want to do some LabVIEW processing of the spectral data as it comes in (like plotting it, averaging it, etc.) -- this is also not specified.

This sounds like a classic Producer/Consumer design -- you have one loop (the Producer) connected to your instrument, gathering spectra every so often, and putting an array of spectral data into a Queue that goes to the Consumer.

The Consumer can do several things.  One is write the data as a column to an already-opened Excel Worksheet.  Another is to (possibly) make a LabVIEW plot of the data.

If this is all you need to do as far as saving the data to Excel, it is really very simple.  The Report Generation Toolkit has a function Excel Easy Table that takes a 2D array of strings or numbers and writes the entire array to Excel starting in a specified location.  If you are writing a row at a time, you take your 1D row array, pass it through Build Array to make it a 2D 1-by-N "row vector", and Excel will write this at the row you specify.  If you want it to be a column array, you add the step of transposing the 2D 1-by-N into an N-by-1 "column vector".  In either case, Excel Easy Table will give you the place to write the next Row or Column.

You do all of the writing inside a loop.  Before the loop, you open the Report as I showed in my snippet, and after you have done all of the writing and have exited the loop, you save it (which does the Excel Write) and "dispose" the Report (which closes Excel).

I start Jury Duty tomorrow, but will check back here in the evening.  I hope I'm being helpful to you.

Bob Schor

[b9]

to bob, heres the flow which i have in my head on how i was hoping for it to work as i want to read the data to plot the spectrum on labview at real time.

1) device enters data into excel by column.
2) the read from spreadsheet vi will read tht current column and plot the spectrum.
3) new data comes in from device and adds on to the workbook.
4) read from spreadsheet vi is now suppose to read the updated workbook which it doesnt since it only reads the previous workbook version and not the updated version and that can only be done by saving the excel file first before the file path for read to spreadsheet vi is updated.

step 4 is my problem at the moment as i have no idea how to make excel save itself automatically and i was hoping that by reading the data dynamically it will somehow save the excel file in the process and labview can read the data to continuously add on the new plots to the current plot.

i hope this is gives a much better picture of what im doing.

[Ksin301]

Hi Ben, I'm Ksin.. 

I doing the same thing also wondering could I use the method you suggest? But I only have LabVIEW 2010, could you change the file to 2010 version? I would like to learn something new from you :). 

My problem is to read the keep update data column in Excel file, and upload to LabVIEW to do analysis. A little similar with the file you mention to b9. I would like to have look how to use ActiveX to read the only column keep updated Excel file.

Thank you 🙂

[Hassan80]

Hello ben64,

I would like to use your VIs to read an excel file dynamically. My Labview's version(13.0.1) is older than yours, would you please reload them in an older version?

Thans,

[Bob_Schor]

Wow.  The trial on which I was a Jury member lasted 2-3 weeks, but the original poster never followed up, so I was never able to tell him he can (probably) do exactly what he wants to do using the RGT and "true" Excel (that is to say, a .xls or .xlsx file, not what LabVIEW calls a "Spreadsheet File", which is really a Comma-Separated Variable (.csv) text file).  And now, 5 months later, two people individually "hijack" this thread and say "Me, too".

Here's a suggestion -- each of you, start a new thread.  Explain clearly what you want to do.  Be sure to distinguish between "Excel" (a program, part of Microsoft Office, that reads/writes .xls and .xlsx files that have some built-in "intelligence" to them) and a text file, with (numeric or string) data arranged in lines, entries within a line separated by commas (a .csv file).  Describe the nature of the data you want to read and/or write, how you want it organized (are you writing a row at a time, a column at a time, a 2-D block of data), and what kind of processing you want to do.  If creating a file, it would be useful to know the expected data rate, and how long the acquisition will last (writing a row of 20 points 100 times/second for 5 minutes is very different from writing 100 points once an hour for 5 months).

If you've read this thread carefully, you know that LabVIEW has functions that can read/write .csv files built-in to the File Palette -- there are numerous "how-to" examples on the Forum, and if you are having difficulty with using these functions, scour the Forum for examples (it's OK to copy), and before asking for help, post your code -- we're not here to do your work.  [Post code -- that means VIs or Projects (zipped up, of course)].

If you really want to use Excel, your choices are ActiveX (which is a little tricky, "old-fashioned", but (mostly) works) or the NI-supplied Report Generation Toolkit (a LabVIEW add-on that became part of "base LabVIEW" with LabVIEW 2014).  I've used both, but since the new RGT was introduced (around LabVIEW 2009?), I've only used the RGT and find it extremely powerful and easy to use (though not always optimally documented).  Again, there are a lot of examples here in the Forum (I've written a number, myself) -- Seek and Ye Shall Find.  When asking for help, post your code.

Bob (RGT) Schor