Shut Down Script.....

[deevaetodin]

Hi,

I am using the following code launched with autoexec macro to open up an excel sheet when the database is opened automatically.

Function OpenExcelAp()
Dim objExcel As Object
Dim file As String
file = "N:\test.xls"
Set objExcel = New Excel.Application
With objExcel
'Open the file
.Workbooks.Open FileName:=file
'Make it visible
.Visible = True
.Application.WindowState = xlMinimized
End With

End Function

What I need to do is have another script that will automatically shut and save the excel application when the Access database is closed. How can I alter this script, and I assume this code could then be attached to a command button that would shut down everything.

Any help is appreciated.

D.

 

[RickSpr]

I believe you should make your objExcel variable a Public variable in the Declarations section of the module. (It must also be moved to a standard module, if you have it in a Form or class module now). Then you should be able to shut it down with the statement "objExcel.Quit".

In fact, I'm surprised that the Excel object isn't destroyed when your function returns, as objExcel in its current location gets set to Nothing at that time. Perhaps it persists because you made it visible, in which case you may have to make it invisible again before shutting it down.

Incidentally, if you add Microsoft Excel 9.0 Object Library to your References (on the Tools menu), and change the variable to Dim objExcel As Excel.Application, it should be more efficient. When you Dim a variable As Object, the runtime has to look up each method call on the fly, whereas if you Dim it as the specific object, the compiler can do the looking up. You'll also get the IntelliSense lists for properties, methods, and parameters that way. Rick Sprague

 

[deevaetodin]

Rick;

Thanks for the information.

I will see what I can come up with using your suggestions.

Once again, thanks.

D.