I have a similar problem and after trying all the suggestions giving I have been unable to resolve it. I am using Win98, excel 2000, and vb6. I am opening an existing excel file from vb, editing a large number of cells, and closing it. I am doing the editing in a loop. When the loop variable is small everything works as expected. When I increased the variable excel no longer closes. It remains in the running processes. When I increase it even more, I get a component request pending error. Excel stops responding to the vb program. I thought I had an issue with RAM but I increased that and it didn't seem to make a difference. Any ideas?
My code:
Public xlApp As Excel.Application
Public xlWb As Excel.Workbook
Public xlWs1 As Excel.Worksheet
Public xlWs2 As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open("C:\WINDOWS\desktop\Tonya\2Counters\FreqData.xls")
Set xlWs1 = xlWb.Worksheets(1)
Set xlWs2 = xlWb.Worksheets(2)
'begin speed optimization
xlApp.Calculation = xlCalculationManual
xlApp.ScreenUpdating = False
'Loop
'Edit spreadsheets here
'End Loop
'end speed optimization
xlApp.Calculation = xlCalculationAutomatic
xlApp.ScreenUpdating = True
xlWb.Save
xlWb.Close
xlApp.Quit
Set xlWs1 = Nothing
Set xlWs2 = Nothing
Set xlWb = Nothing
Set xlApp = Nothing