Problem with Excel 2000 automation

[Jack]

Hello,
I do not have that problem when using Excel 2002 and up, but
when using Excel 2000 the problem is as follows:
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If

When there is not Excel application running and the code above is executed,
the Excel.EXE starts as a process.
Later, when the user wants to open spreadsheet the spreadsheet will not
open (Excel 2000 only) and the only remedy is to terminate Excel.EXE process
using Task Manager.
How to prevent that situation?
Should I programmatically terminate Excel.EXE process or there is another
way?
Any ideas appreciated,
Jack

 

[Richard Mueller [MVP]]

Hello,
I do not have that problem when using Excel 2002 and up, but
when using Excel 2000 the problem is as follows:
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If

When there is not Excel application running and the code above is
executed, the Excel.EXE starts as a process.
Later, when the user wants to open spreadsheet the spreadsheet will not
open (Excel 2000 only) and the only remedy is to terminate Excel.EXE
process using Task Manager.
How to prevent that situation?
Should I programmatically terminate Excel.EXE process or there is another
way?
Any ideas appreciated,
Jack

I use code similar to:
==========
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Code to write to the spreadsheet.

' Save the spreadsheet and close the workbook.
strExcelPath = "c:\Myfolder\MyWork.xls"
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit
========
If I'm only reading the spreadsheet I use:
=========
' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
==========
If I fail to Quit, I must terminate the excel.exe process in task manager or
the spreadsheet is locked.

 

[Jack]

Are you talking specifically about Excel 2000?
What is the cause of the spreadsheet lock? How to avoid it?
Why are you using Task Manager to terminate process?
Cannot it be done programmatically?
Thanks,
Jack

 

[Peter T]

Following (tested in Word) works fine for me with XL2000 and am not aware of
any particular difference in behaviour between XL versions

Dim moExcelApp As Object

Sub StartXL()
Dim sMsg As String
On Error Resume Next
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
sMsg = "new instance"
Else
sMsg = "existing instance"
End If

MsgBox sMsg
moExcelApp.Caption = "Hello"
On Error GoTo 0
moExcelApp.IgnoreRemoteRequests = True


' moExcelApp.Visible = True ' for testing
End Sub
Sub QuitXL()
Dim oWB As Object
On Error Resume Next

' careful - close all without saving - only for testing

' first destroy any object ref's to objects on sheets

moExcelApp.DisplayAlerts = False
For Each oWB In moExcelApp.Workbooks
oWB.Close False
Next

' destroy any sheet object ref's

moExcelApp.IgnoreRemoteRequests = False
moExcelApp.Quit

Set moExcelApp = Nothing

End Sub

Don't simply use the above as a template, particularly the
IgnoreRemoteRequests pair which you probably wouldn't want with GetObject
and not necessarily with CreateObject. But look into why I included it.

If you want an XL instance purely for your own purposes normally best not to
bother with getting any existing instance, create your own anew. If feasible
make it visible in case anything goes wrong, though there may be other good
reasons not to do that.

The important thing when quitting is to explicitly destroy all object refs
that are not about to go out of scope by themselves, in the order they were
created, eg

set moRng = Nothing
set moSht = Nothing
moWB.close ' false true saveas, whatever
set moWB = Nothing
moExcelApp.Quit
set moExcelApp = nothing

Regards,
Peter T