Excel called from external app (OLE) stops after processing exactly 10921 rows

[Peter Gloor]

Hello NG,

When I call Excel using OLE from an external application the processing of
large Range objects stops after exactly 10921 rows have been processed. The
range contains 30,000 rows and 6 columns. Excel is Excel 2002 (SP2) and the
OS is Windows/ME.
The PC is a DELL Dimension 8200 with 512 MB RAM.

Has anybody an idea how to get the problem solved. I need a workaround to
process about 60,000 lines. Splitting the ranges and copy the pieces into
multiple worksheets is not an option (except if this can be done fully
automated from the external application).

The problem can be reproduced in Excel using the Macro Sub Test() near the
end of this message. In order to reproduce the problem you will need two
Excel files:

A) Excel file with data:
1. Create a new Excel workbook
2. In the first sheet fill the range A1:F30000 with data.
3. Make sure the worksheet is labelled as "Table1".
4. Save the file as c:\testdata.xls.
5. Close Excel.

B) Excel file with macro to reproduce the problem:
1. Create a new Excel workbook
2. Open VisualBasic editor.
3. Paste the code below into the code window.
4. Save the file as c:\testmacro.xls.
5. Make the Direct Window visible (for Debug.Print output)
6. Run Sub Test() from VisualBasic editor.
7. The macro hangs after i becomes 10922.
Warning: the whole system hangs (Windows/ME). You will need to kill manually
each open Excel task and to reboot the system.

--- cut from here ---
Sub Test()
Dim xlApplication As Application
Dim xlWorkbook As Workbook
Dim xlWorksheet As Worksheet
Dim xlRange As Range

Dim strData1 As String
Dim strData2 As String
Dim strData3 As String
Dim strData4 As String
Dim strData5 As String
Dim strData6 As String

Dim i As Long

Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Open("c:\TestData.xls")
Set xlWorksheet = xlWorkbook.Worksheets("Table1")
Set xlRange = xlWorksheet.Range("A1:F30000")

For i = 2 To xlRange.Rows.Count
Debug.Print i
strData1 = CStr(xlRange(i, 1).Value)
strData2 = CStr(xlRange(i, 2).Value)
strData3 = CStr(xlRange(i, 3).Value)
strData4 = CStr(xlRange(i, 4).Value)
strData5 = CStr(xlRange(i, 5).Value)
strData6 = CStr(xlRange(i, 6).Value)
Next
End Sub
--- cut to here ---

Thanks for any idea or help.

Peter

 

[Tushar Mehta]

I cannot duplicate the problem on a Win XP Pro / XL 2002SR1 system. It
is possible that the Win ME version doesn't hang but gets progressively
slower as system resources are consumed. Though, even that is hard to
imagine for the code you shared.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peter Gloor]

Thank you Tushar for taking the time (and the risk of a system crash) to
test this.

I'm out of ideas. It performs about the same for row 2 to row 10921. I can't
see any performance degradation. The system monitor shows 100% CPU, but no
remarkable changes in memory usage. The resource display show 62% free for
user and system resourcs and 86% free for GDI resources.

I tried to split the range into subranges (each 1000 rows). No change. Hangs
after 10901 rows have been processed. I'll try this again with some kind of
wait in between, to make sure Excel can follow and gets enough time to
cleanup resources.

I tried to start at row 10920 (to make sure the data are ok.). This
processes row 10922 but hangs about 10900 rows later (21841 or so).

Unfortunately this must run on some Windows/ME and Windows/NT systems. I
could not yet test on Windows/NT.

Peter

 

[Peter Gloor]

Just an update:

I'm out of ideas. It performs about the same for row 2 to row 10921. I can't
see any performance degradation. The system monitor shows 100% CPU, but no
remarkable changes in memory usage. The resource display show 62% free for
user and system resourcs and 86% free for GDI resources.

Sorry, the information about free resources is wrong. The actual values are:
47% free user and system resources
71% free GDI resources.

In addition the System monitor shows a peak with disk write (and some read)
activity just before the system hangs.

I tried to split the range into subranges (each 1000 rows). No change. Hangs
after 10901 rows have been processed. I'll try this again with some kind of
wait in between, to make sure Excel can follow and gets enough time to
cleanup resources.

The implementation of a Wait (Application.Wait) for one second each 1000
rows didn't help either.

Peter

 

[onedaywhen]

How about using ADO and SQL to manipulate your Excel data?

--

 

[Tushar Mehta]

Hi Peter,

Glad you got that resolved.

Thanks for sharing the solution you came up with. My guess is that
what you are doing is causing Win ME to clear out whatever is drawing
down resources.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions