topic Re: read cell value from Excel for multiple files in LabVIEW

read cell value from Excel for multiple files

LMM — Sun, 12 Aug 2012 18:29:59 GMT

I'm trying to write a LabView routine to read a specific cell value from an EXCEL worksheet file. I'd like to plot the value then move onto the next EXCEL file in the folder. In one of the forums I found this LabView VI that works to read a cell value from a single EXCEL file called Read_Value_From_Excel.JPG

I tried to modify the program by adding a For Loop and List Folder function as shown in Read_Value_From_Excel_LM2.JPG. This file gives me an error message indicating "Incorrect function in Read_Value_From Excel_LM2".

I would appreciate any help as I've been struggling with this for a few days and haven't been able to find a similar question in the other forums. Thank you.

Re: read cell value from Excel for multiple files

RavensFan — Sun, 12 Aug 2012 18:40:28 GMT

I see two major problems with your code.

In both screenshots, you are closing all of your references as pretty much the same time. There is a race condition as to which of the close functions executes first. You need to close your references in the reverse order of how they were opened. Otherwise you'll wind up closing a higher level reference such as a worksheet before you close a lower level reference such as a range in that worksheet.

The second problem is that you are closing the application reference inside the For Loop, but you need that reference again in the next iteration of the For Loop. The close application reference should be after the For Loop.

Re: read cell value from Excel for multiple files

LMM — Tue, 14 Aug 2012 15:49:21 GMT

Ravens Fan, thank you for your reply. I think I understand your comment that EXCEL is being closed before all of the files are processed, I can see that when I use the step thru function to run the program.

I tried simply moving the Invoke Node for closing the application outside the For Loop and I can't get my wiring correct. I tried a few different permuations and couldn't get things to work. I'd really appreciate additional comments as I'm pretty stuck.

Attached is the .vi file (with the broken wiring). I'm using LabView 8.6. Thanks again.

Re: read cell value from Excel for multiple files

Ken_Naylor — Tue, 14 Aug 2012 16:29:51 GMT

Right click and select 'Disable Indexing' on the top two exit terminals and the broken arrow goes away. You only need to close 1 Application reference not an array and to do this you only need 1 error cluster not and array.

Hope this helps

Ken

Re: read cell value from Excel for multiple files

RavensFan — Tue, 14 Aug 2012 19:32:34 GMT

Ken's answer is correct about how to fix your broken wire issue.

But you still haven't fixed the problem with trying to close all the references at the same time. Please look in the example finder for Excel examples so you can see the proper way to do it. You should be using the error wire to define the order of execution of all those close reference functions rather than having them all wired in parallel by splitting the error wire.

Re: read cell value from Excel for multiple files

LMM — Wed, 15 Aug 2012 06:18:25 GMT

Raven's Fan and Ken, thank you very much for your help. I'm still having trouble with the "close reference" functions. From what I've read I think I should be closing the reference functions in the opposite order the nodes were open. Am I on the right track to move some of the close references into the loop and leave the close reference for the application outside? Also, I removed the wiring for the error inputs and outputs so I can focus on getting the close references correctly wired.

Thank you again.

Re: read cell value from Excel for multiple files

Ken_Naylor — Wed, 15 Aug 2012 07:11:03 GMT

Yes you need to close the references in the reverse order and indeed moving them inside the loop will help but you still need to determine the order in which these references close by connecting the error cluster on each close reference with a single error cluster wire not multiple branches

Ken

Re: read cell value from Excel for multiple files

GPeter — Wed, 15 Aug 2012 07:14:43 GMT

Hi,

I would say to use error wire to grant the right sequence order of closing the references.

In your last vi, the reference can be closed even before getting the data out of the file.

Br,

Peter

Re: read cell value from Excel for multiple files

RavensFan — Wed, 15 Aug 2012 14:51:37 GMT

@GPeter wrote:

Hi,

I would say to use error wire to grant the right sequence order of closing the references.

In your last vi, the reference can be closed even before getting the data out of the file.

Br,

Peter

That is exactly correct. I don't understand why the OP didn't understand that when I said that in my previous messages. Instead he decided to just ignore error handling altogether. Sometimes I wonder what people are dreaming about when they read a message and come to a completely different conclusion about what they should be doing.

People, when you are told to do something, please give us the courtesy of actually trying it out. Ignoring suggestions doesn't help you solve your problems and just shows you don't value our time we put into helping you solve the problem. If you are going to waste my time, what motivation do I have to continue helping you?

Re: read cell value from Excel for multiple files

LMM — Wed, 15 Aug 2012 15:05:16 GMT

Thank you very much for your responses.

Ravens Fan, I really apologize. I honestly wasn't trying to modify away from your suggestions. The problem is that I'm not fully understanding what all the functions are doing and so I wound up going in the wrong direction. I'm trying to use various resources off the Web to learn this but it's been slow going. I'll give the .vi another try based on the suggestions. Thank you again for all your patience.

Re: read cell value from Excel for multiple files

LMM — Fri, 17 Aug 2012 06:31:58 GMT

Thank you for everyone's help and comments.

I reworked the error wires for the "close reference" functions. The program output still isn't correct. By running in highlight execution mode I can see that the correct file is going into the Invoke Node "Workbooks/open". But out of that node there's an error that reads "ERR -2146827284". I can't find information on this error code. I would really appreciate additional help. Thank you again.

Re: read cell value from Excel for multiple files

Ken_Naylor — Fri, 17 Aug 2012 07:14:03 GMT

That error covers a number of error in the Excel Application interface. In this case I think it is a 'File Not Found' Error. The Filename string input to the 'Workbooks/Open invoke node must include the path to the files. Add a Build path and path to string just before the input to Filename

Ken

Re: read cell value from Excel for multiple files

Frabto — Fri, 17 Aug 2012 07:15:22 GMT

1.The vi expects the file path..you have wired just the file name.

2.As previously mentioned you have closed the workbook refnum early before coming to second iteration.

3.I dont see any use of while loop there.

Attached the modified vi

Re: read cell value from Excel for multiple files

LMM — Fri, 17 Aug 2012 16:52:17 GMT

Nijams, thank you for your message.

I am unable to open the file you posted. I'm usng LabView 8.6 and I'm getting the error that your .vi is a newer version 9.0. Is there a way that the file can be downconverted so I am able to read it? Thank you again.

Re: read cell value from Excel for multiple files

LMM — Sun, 19 Aug 2012 00:21:16 GMT

I was able to kluge together this program that seems to be working ok. I think it will work for my purposes. Thanks a lot for all the help.

Nijams if it might be possible to convert the program you posted on 8-17 from LabView 9.0 to 8.6 I'd greatly appreciate it so I might access it and study your solution.

Re: read cell value from Excel for multiple files

Tim_O1 — Mon, 20 Aug 2012 18:05:43 GMT

Hi LMM,

You can also try posting to the forum linked below to get a VI converted.

Version Conversion
http://forums.ni.com/t5/Version-Conversion/bd-p/VersionConversion

Tim O

Re: read cell value from Excel for multiple files

LMM — Mon, 20 Aug 2012 19:20:34 GMT

Thank you Tim O. I just posted to the forum you suggested.

Re: read cell value from Excel for multiple files

Frabto — Tue, 21 Aug 2012 05:02:46 GMT

Unfortunately i was not able to down convert.I have attached the snippet

Re: read cell value from Excel for multiple files

LMM — Wed, 22 Aug 2012 05:16:02 GMT

Nijams, thank you for your message. Your VI was converted at this site:

http://forums.ni.com/t5/Version-Conversion/Down-convert-from-LabView-9-0-to-8-6/m-p/2129848

Re: read cell value from Excel for multiple files

ellappan.perumal@gmail.com — Sun, 23 Dec 2012 10:16:14 GMT

hi is it possible to read the excel next ,next.....cell values automatically

thanks in Advance