Filip,
Here are some general guidelines to use when automating Excel.
Regards,
Jim Cone
San Francisco, CA
'-----------------------------------------------------------------
1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet
Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)
Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))
2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.
3. Avoid the use of the "With" construct.
4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=False 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing
Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.
'------------------------------------------------------------
Articles dealing with unqualified references and automation application not quitting.
1. 178510 - PRB: Excel Automation Fails Second Time Code Runs
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b178510
Summary: While running code that uses Automation to control Microsoft Excel,
one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error:
Run-time error '1004': Method '<name of method>' of object '_Global' failed -or-...
2. 189618 - PRB: Automation Error Calling Unqualified Method or Property
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b189618
Summary: While running code that uses Automation to control Microsoft Word 97, Word 2000, or Word 2002,
you may receive one of the following error messages:
Run-time error '-2147023174' (800706ba) Automation error -or- Run-time error '462': The remote server...
3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory
http://support.microsoft.com/default.aspx?scid=KB;en-us;q199219
When you run a macro that uses automation to create a Microsoft Excel object (instance),
the Excel object does not exit from memory when you specify.
If you create another Excel object after quitting the first, a second instance is in memory.
This problem occurs when your macro uses a "WITH" statement that refers to the automation object.
4. 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic
http:// support.microsoft.com / default.aspx?scid=kb%3ben-us%3b319832
Summary: When you automate a Microsoft Office application,
you may receive an error message or you may experience unexpected behavior, as follows.
You may receive one of the following error messages: Error 91: Object variable or With block variable not set....
'------------------------------------------------------------
