Can LabVIEW read selected row/column location in open spreadsheet ?

[biernaskieb]

Instead of asking user to manually input LabVIEW data (desired row/column location for data entry into open spreadsheet), can LabVIEW read the selected row/column location in an open spreadsheet selected by the user ?

If so, then LabVIEW now has a starting location for user to start inputing data without having LabVIEW to ask user for it !

Any ideas about how to accomplish this would be much appreciated !

Thanks !

Barry

[smercurio_fc]

If you're referring to Excel workbooks then you need to use ActiveX to interface to Excel. You can use (buy) the Office Reporting Toolkit from NI or you can use the plethora of examples posted in this forum or in the Excel thread. Searching works. Note: Do not post in that thread - it is a repository of links only. In that case you can provide the starting location on the VI's front panel and just read a range of cells.

[biernaskieb]

Yes, using LabVIEW, I have already opened and inputed data into various pages of the Excel Workbook by having the user supply the page # and row/column # manually via text box's on the Front Panel. So you are saying I have to use Active X to interface or "read from" the selected or active Row/Column # of the Excel spreadsheet ?

Thanks !

Barry

[Faraclas]

For Excel automation I generally only use the VIs that are in the Report Generation Toolkit to open/create, save/close the excel sheet, or insert data.  For all other interactions I would do the following:

1. Create a macro in Excel that performs the functionality (reading, formatting, calculating, graphing, etc.)

2. Copy the macro (could be a collection of subroutines/functions) into a string constant in LabVIEW

3. Use the toolkit in labview to add your Visual Basic code to the Excel worksheet progrmatically (this avoids having to use a template)

4. Use the run macro function in the Toolkit to execute the macro.

Doing things in this manner alows you to develop your Excel code in the integrated VB IDE.

Perhaps this does not answer your question directly, so here is some other food for thought.  The excel sheet should only be considered as an input (data source) or a report (data sink).  If you want to programatically interact wiht the data, the leverage the full power of LabVIEW.  That is, load in all of the data that might need interaction with into LabVIEW (this is more than the selected data, rather it is all of the data that can potentially be selected).  Interact with the data in LabVIEW using a nice LabVIEW GUI.  When you are done with the data, update the Excel sheet.

One more set of tips.  If you are going to do heavy interaction with the Excel sheet (back and forth), it would probably be easier just to do the interaction in Excel using Visual Basic.  If you just need to read/write data, then LabVIEW is the place for the interaction.  For heavy automation, do the coding in visual basic and initiate the running from LabVIEW.

Hope this helps...

[chuggins143]

I have a fare bit of automated testing happening in my lab using LabVIEW and Excel and what I do is the "hole in the tree" method of transfering data between the two... For my dial in points I have a macro in Excel that writes the points to a text file and have LV watching that file... For getting data that I've taken back to Excel, I have LV write to a text file and then run a macro in excel that pulls the data in to a specified location and then deletes that file.  Seemed to be a much simpler way to go than trying to futz with ActiveX and such. 

C

[biernaskieb]

Thanks for your reply !

I knew of VB Code and Macros running independently of LabVIEW but not together as you mentioned below ! This will definitely come in handy for those heavier interactions with Excel !

Right now, I just need to read/write basic data, so I think LabVIEW is the tool of choice for this ! I just wanted to see if there was an easy way to read the row/column number from the spreadsheet using LabVIEW so I don't have to ask the Operator for it as the starting location for data entry.

Thanks !

Barry

[Faraclas · Accepted solution]

For what you want to do here, read and write to the active cell.  This example shows you the basics...

NI Example

[biernaskieb]

Excellent ! This is exactly what I am looking for and wanting to do !

Thanks very much !

Barry

[chuggins143]

Barry,

Just out of curiosity, is there any reason that LabVIEW needs to know the cell address?  If you're using macros then you should be able to have the macros take care of keeping track of what's going where and leave the data acquisition to LabView.  Either way is quite acceptable, but I just found that having to jump through all the hoops to get LV to talk to Excel directly was way too cumbersome.

Chad

[biernaskieb]

RIght now, my LabVIEW App asks the operator to open their specific spreadsheet from whatever location then asks what Spreadsheet Page # to open. LabVIEW then asks for the operator initials to be input into the Front Panel Text Box for Spreadsheet entry. After proper initialization routines have executed successfully, weight data is then read from a scale and LabVIEW then asks the operator to input the starting row # for data entry into the spreadsheet. Instead of doing this, I thought it would be easier and less operator dependant to have LabVIEW detect the selected or active cell in the spreadsheet for the starting data entry point. I have very limited knowledge of Excel Macros and what they can do so I thought to purse the LabVIEW method first. The first weight data entry is dynamic (not always the same page and cell location as a starting point) as it is totally operator dependant as where they want to start entering data first. So I don't know if Excel Macros would successfully accomplish this particular task/request or not.

Thanks for your valued input !

Barry