Opening Excel But External Data Not Being Read.

[NToombes]

Hi all

Wonder if someone can help. So, we have a furnace with a data recorder that's saving the measured data (temperatures & vacuum) to a text file in CSV format. An excel file has been created that uses "get external data sources" functionality to read this csv file in automatically when opened each time and then a separate worksheet creates a graph which we print out. this works absolutely fine when opened manually. So, as we are now requiring this recorded data to be saved into an SQL database I have written some Labview code that saves the .csv data into a database fine but also want to automate opening the excel file and printing it out. #Using the report generation toolkit I have automated the opening, printing and closing of the excel workbook but for some reason when Labview opens the excel workbook it does not "execute" the reading in of the .csv file and prints out an empty file. I have tried selecting "Nothing, Maximised, Minimised and No Change" on the options for opening Excel but nothing. Again, I open the file by double clicking the excel workbook everything is fine. 

So, my question is, any idea why when opening the excel file from within Labview, why the external data is not being read in but its fine if I open the file manually.

Thanks for any help

Neil

[Bob_Schor]

First of all, a .csv file is not an "Excel file", it is an ordinary text file where the text data is in "comma-separated value" form, where strings (which can represent numeric values) are separated by commas to signifies "columns", and End-of-Line codes (typically \r\n) are use to delimit "rows".  Note that LabVIEW's "Read/Write Delimited Spreadsheets", designed for .csv files, uses a tab character (\t) as the default delimiter, for (I'm guessing) "historic" reasons (meaning "we made a mistake doing this many versions ago, and don't want to change the default to an actual comma").

Use Read/Write Delimited Spreadsheets for .csv files.  Use the Report Generation Toolkit to process Excel files (meaning .xls, .xlsx, and others).

Bob Schor

[ben64 · Accepted solution]

It seems Excel doesn't refresh the external connexion when opened programmatically. Try the following code to force a refresh of the connexions.

Ben64

[NToombes]

HI Bob

Thanks for your answer to my question. Maybe I didn't explain my problem very well but thankfully Ben below has solved the issue for me. I was using the "Report Generation Toolkit" to open Excel programatically which it was doing fine but my Excel workbook has external data sources that were not being updated when Labview was opening the file but its was updating when I manually opened the file from a double click. 

Anyway, the ActiveX solution Ben came up with below, from my first impressions seems to work. Putting it into practise today. 

Thanks

Neil

[NToombes]

Hi Ben64

Thanks for your solution, it seems to work from a few tests but putting the fully automatic system into play today and see how we go.

To be honest, I did start looking down the ActiveX route on Friday but there are so so many "references" and "sub-references" that I didn't know which ones to use. Is there a website with a good list of all the Active X commands available in Excel that might be of use to me?

Thanks once again

Neil

[ben64]

Hi Neil,

I usually use the Microsoft Excel Developer Reference for the details of the properties and methods and do some google search to figure out which of these properties and methods to accomplish a specific task.

Ben

[NToombes]

Hi Ben

Thanks for that, much appreciated. Its a good resource to keep handy.

Neil

[aputman]

I use macros to see how certain tasks can be accomplished in ActiveX.  Record the macro and then look thru the generated code and it will show you the steps to get the same outcome.