Creating an private Excel.Application

[Chet Cromer]

I have an application that uses an Excel.Application object to print
numerous reports and other information while the program is running.
Currently I create an excel.application each time I need to create a report
and then .quit it and set it to nothing when I'm done. This causes quite a
delay when you have to keep opening and closing Excel. What I'd like to do
is have a single Excel.Application that runs the whole time the application
is running. The problem with this is that if the user goes elsewhere and
double clicks an Excel file, it opens up in my application's object. THen
when they exit Excel, my object is no longer valid. How can I create an
Excel.Application object that isn't accessible to the user when they double
click a file to open it?

Thanks,

Chet

 

[Michel Pierron]

Hi Chet,
You can test:

Private Sub Workbook_Open()
Application.OnWindow = ThisWorkbook.Name & "!ThisWbkOnly"
Application.DisplayAlerts = False
Application.IgnoreRemoteRequests = True
Application.DisplayAlerts = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnWindow = ""
Application.IgnoreRemoteRequests = False
End Sub

In a standard module:
Sub ThisWbkOnly()
If Not ActiveWorkbook.Name = ThisWorkbook.Name Then
ActiveWorkbook.Close False
MsgBox "Excel private intance !", 64
End If
End Sub

Regards,
MP

 

[Tom Ogilvy]

Set this at the beginning after you create the application
With Application
.IgnoreRemoteRequests = True
End With

Set it back just before you close the application and before you close the
last workbook.
With Application
.IgnoreRemoteRequests = False
End With

Haven't tested it, but I believe this will do what you want.