LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Error -2147024882 when reading excel sheet

Solved!
Go to solution

Dear Community,

Attached you will find the vi I am using to read an excel sheet containing approx. 2000x2 string entries. An interesting thing happening is that systematically every other time i get Error -2147024882, LabVIEW:  (Hex 0x8007000E) Ran out of memory. I have not yet discovered what the reason behind this behaviour is. Any ideas?

Thanks in advance

0 Kudos
Message 1 of 10
(827 Views)

i would be prudent to add your excel file


If Tetris has taught me anything, it's errors pile up and accomplishments disappear.
0 Kudos
Message 2 of 10
(809 Views)

The think I forgot to mention is:

I am using LabVIEW 2012

Windows 7

And Excel 2013

0 Kudos
Message 3 of 10
(805 Views)

Yes, we need to see the data that is causing what seems to be an abnormal termination.  Because you attached your VI (thank you), we could tell you were using LabVIEW 2012.

 

Bob Schor

0 Kudos
Message 4 of 10
(791 Views)

Here is the Excel File.

Rename the file in the VI please.

 

Admin Note - Removed attachment per user's request

0 Kudos
Message 5 of 10
(782 Views)

OK, I see "a problem" (slightly different), but also a solution.

 

I'm running on a Windows 10 VM, with LabVIEW 2018 and Office 2013.  Note that in LabVIEW 2018, "New Report" has been deprocated (I don't think it matters, but I used Create Report, the new function that drops the Standard Report).  When I run your code against your Excel file, it basically seems to take forever (I generally abort-quit after a minute or so).

 

However, if I tell Excel Get Data to read from [0, 0] to [1999, 1] (by wiring Start and Stop inputs), the program finishes in less than a second with a 2D array of strings.

 

The RGT was really written to write reports.  The "Read" for Excel is, I think, something of an after-thought that can work, but you need to be careful ...

 

Bob Schor

0 Kudos
Message 6 of 10
(774 Views)

Hi,

 

checking the size of the "UsedRange" of your worksheet in your excel-files yield 16384 columns and 1977 rows.

 

That is quite a lot of cells to read.

 

Regards, Jens

Kudos are welcome...
Message 7 of 10
(770 Views)

@JensG69 wrote:

 

checking the size of the "UsedRange" of your worksheet in your excel-files yield 16384 columns and 1977 rows.

 


That triggered a memory of a similar problem I encountered (but forgot about!) a while ago.  The UsedRange property of Excel Worksheets is strange and difficult to modify.  But there's a trick:

  1. Open the file Debug.xlsx.
  2. Highlight the data area (2 columns and almost 2000 rows).
  3. Copy (Ctrl-C).
  4. Open a new Workbook.
  5. Paste (Ctrl-P).
  6. Save the Workbook as, say, "Fixed Debug.xlsx".
  7. Use the fixed workbook.  It will work fine.  It will be slower than if you actually specified "Read 2000 rows, 2 columns" as it needs to figure out how many rows and columns there are, but I'd say it takes about 1.7" without specifying (as opposed to "until it runs out of memory") and maybe 1.3" with a 2000 x 2 read.

I also tried it with your LabVIEW 2012 code running in LabVIEW 2018, and it behaves the same way (about 1.7" to read everything).

 

Bob Schor

Message 8 of 10
(762 Views)

The problem is caused by your Excel sheet having a lot of stuff on line 1499 from column FG (157) to  column XFD (16384). Since a range is always single or multiple rectangular areas you end up with range size 1677 X 16384.

 

Ben64

--------------------------------------------------
The best way to say thanks is to give kudos!
Message 9 of 10
(758 Views)
Solution
Accepted by topic author labviewette

As pointed out already, the problem is in your Excel file and not your code.  The RGT is smart enough to default to the UsedRegion when the Start and End inputs are left unwired.

 

However, you can fix this issue in LabVIEW if you can't fix the problem with the Excel file.  Just use the invoke node "Range" with property node "CurrentRegion" instead and set the End Location.  Here is the VBA equivalent:

ActiveSheet.Range("A1").CurrentRegion.Rows.Count

ActiveSheet.Range("A1").CurrentRegion.Columns.Count

 

Use the RGT function Excel Get ActiveX References to obtain the WorkSheet reference.

Michael Munroe, CLD, CTD, MCP
Automate 100+ VI Search and Edit operations with Property Inspector 3.0, now with Global Exclusion™ and Persistent Selection™!
See how to edit over 1000 VIs in under 2 minutes on Youtube
Message 10 of 10
(753 Views)