I finally found a solution for my problem. I post it here because it might
help others in similar situations.

The trick is to close anything and release all objects each 5000 rows,
execute a DoEvents, and restart anything before processing the next 5000
rows. The DoEvents is very important! Closing the application is not enough.

The problem seems to appear in Windows/ME only. Me and others could not
reproduce the problem with Windows/XP or Windows 2000. We have not tested on
other systems.

Here is my solution:

Option Explicit
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
Dim retstat As Integer

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
If (i Mod 5000) = 0 Then
' close workbook and application
Call xlWorkbook.Close(False)
Call xlApplication.Quit
retstat = DoEvents()

' Free any object
Set xlRange = Nothing
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApplication = Nothing

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

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

Very tricky. Isn't it?

Peter

"Peter Gloor" schrieb im Newsbeitrag
...
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