-
hey guys, need some help (as always)
I'm using the excel object and my program keeps crashing..
After doing a lot of processing with the ole, excel just stop responding.. I can tell because I have some traps setup to show me the processes in txt's.. Oventually excel will just stop.. and vb gets stuck in this horrible loop msgbox saying "Object stop respond.. choose and option Switch to program to fix or retry.. I can't even cancel"
Is there only so much excel can handle? Should I actually close the file and reopen it or something? Are there any commands I can tweak to give excel more room?
Any help would be great. Thanks!
-
This may or may not help you, but I know that you have to be careful how you call procedures using the Excel object. All of your call statements should be in one procedure. i.e.
Private Sub Excel_Calls ()
Call Open_Excel
Call Load_Data
Call Perform_Data
etc, etc
End Sub
The reason this works is that if you call your procedures in a chain (Example: Procedure 1 calls procedure 2, procedure 2 calls procedure 3, etc) the first procedure does not finish running until all the procedures are finished. This uses up extra resources and may be what is causing your app to hang. Give it a try. If that's not it, we'll just have to find another solution.
-
Everything is in one procedure.. thank though.
-
Is it performing the same procedures over and over again, or are the procedures different depending on a certain condition? Also, how are you connecting to Excel?
-
The same procedure opens , does the work , and closes the excel object.. I fixed it though by taking another route.. I just loaded all the A column into an array, and used that as a point of index instead of having some loops go though the spreadsheet using the object.. It's much faster and it doesn't lock up anymore.. thanks for your ideas though!
peace
-
EXCEL may just be bogged down with recalculations. Each change you make to a worksheet, EXCEL figures out what cells it needs to recalculate, then recalculates. If you are using some features, it recalculates everything after every change. You can change the application.calculation property to xlcalculationmanual and then manually calculate at the end or as needed as shown:
Application.Calculation = xlCalculationManual
...
...
Application.Calculate
