vba program dies

[Peter Kinsman]

I am converting product structures from Excel sheets to Access tables.
There are five workbooks, each for a different part of the company and each
book has six sheets, one of which contains the structures, with products
along the top and components down the side. The program creates a new
Excel.Application, opens each of the WorkBooks in turn and makes the
appropriate WorkSheet Active. It then processes each column that contains a
finished product and adds a record to the Made_From table when it finds a
cell in that column with a formula. I can process the WorkBooks
individually, but when processing them in turn,the program dies while
processing the third WorkBook, taking ccapp and Norton Internet Security
with it. The strangest thing is that if I kill Excel, Access throws up an
Automation error.
I have tried creating the Excel.Application for each WorkBook, but that
gives a runtime error so maybe I would need to call a subroutine five times.
Has anyone any suggestions please?

Many thanks

Peter Kinsman

maybe I should add that this is Office 97

 

[MacDermott]

It sounds as if you're creating too many instances of Excel, and not getting
them killed off.
A quick way to check on this (if you're Windows NT, 2000, or XP) is to look
for extra instances of Excel.EXE in the Processes tab of your Task Manager.)

When you're finished with an Excel instance, make sure you
Close all open workbooks
Quit Excel
Set all Excel variables (application, workbooks, worksheets, ranges,
etc) to Nothing.
Don't count on the procedure going out of scope to do this for you.

HTH
- Turtle