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

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

In article ,
says...
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

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

"Tushar Mehta" schrieb im
Newsbeitrag news:MPG.1aae961ac0b3fd1b98973a@news-server...
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

In article ,
says...
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

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 a
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

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

--

"Peter Gloor" wrote in message ...
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 a
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

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

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

In article ,
says...
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