Error -2147024882 when reading excel sheet

[labviewette]

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

[jwscs]

i would be prudent to add your excel file

[labviewette]

The think I forgot to mention is:

I am using LabVIEW 2012

Windows 7

And Excel 2013

[Bob_Schor]

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

[labviewette]

Here is the Excel File.

Rename the file in the VI please.

Admin Note - Removed attachment per user's request

[Bob_Schor]

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

[JensG69]

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

[Bob_Schor]

---

@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

[ben64]

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

[Michael_Munroe · Accepted solution]

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.