How To Terminate Excel Application Upon Closing Workbook

[zerocul]

To All,

First, any help or advice would be deeply appreciated. Here is the scenario. I have constructed an Excel spreadsheet consisting of three worksheets. The first is a Startup page with navigation buttons and menu options for the user. The second page is the standard form the user is to fill out and the third page is a table that takes the user's information on the form and puts it into the table for tracking and recordkeeping. Here's how it works. The user starts the spreadsheet and from the Startup page selects "New Form". With this the user is taken to the second page and a dialog box is displayed asking the user to input all required values. Once the user has completed this task and selected the "OK" button, the form is filled out and the user is prompted to update the log (table on the third page), asked if he/she wants to print the form and finally asked if he/she wants to email the current worksheet to their supervisor for approval. One of the cells on the form is a log number that has a hyperlink attached to it. The hyperlink references a named cell on the third worksheet in the workbook. When the user emails the activesheet, the supervisor can simply click the hyperlink and be taken directly to the appropriate log number to approve the action. Everything to this point works great. By the way, I am activating "Mail Recipient" to send the activesheet as the body of the email. I do not want to send the file as an attachment. The entire file is too big. My problem is that when the supervisor clicks on the hyperlink within the email, an instance of the spreadsheet is opened in Internet Explorer for editing. Once the supervisor has approved the task and saved changes they close internet explorer. However, the instance of Excel is not shut down. It still shows up in the Processes list in Windows 2000 task manager. If anyone tries to access the spreadsheet again either through the hyperlink or through Excel, errors are generated stating that the spreadsheet is already open. This is because the system as detected a previous instance of Excel. Any ideas how to kill Excel when the supervisor closed Internet Explorer. Thanks in advance...

 

[PHV]

Have you tried to play with the BeforeClose event ?
Something like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
Me.Application.Quit
End Sub

Hope This Help
PH.

 

[zerocul]

PHV (MIS),

Thanks for the advice, however, I already have a BeforeClose Event setup and it has the Application.Quit command in it. What I am finding is that when the spreadsheet is open in Internet Explorer, once it has been modified and saved and the user is exiting, it does not seem to execute the BeforeClose Event.

 

[dyarwood]

Have you opened the spreadsheet from a link? The coding (as far as I have found) does not work in IE. can you open the excel spreadsheet in Excel. I had a similar problem and stopped it by on the computer open My Computer, View,Folder Options,File Types then choose Registered file types and go to .xls exensions and set to open using microsoft Excel.

 

[zerocul]

Dyarwood,

Thanks for the info but all the appropriate file type extensions are associated with the proper application. Everything works great within Excel. The problem lies in sending the activeworksheet as the body of an email with a hyperlink in the email to the appropriate NAMED cell within Excel. When you click on the hyperlink in the email, it opens the excel file within Internet Explorer. When you shut down Internet Explorer it does not completely shut down the Excel application. Excel stays registered as being active in the Windows 2000 processes list. Question? Could the problem lie in the hyperlink. I have code generating the hyperlink and attaching it to the log number. The hyperlink looks like this: file:///p:\some folder\srl.xls#_04.2. Obviously p: is the drive, \some folder is the windows folder the file resides in, srl.xls is the excel file and #_04.2 if the format for referencing a NAMED cell within an excel worksheet. The _04.2 is the name of the cell. Any ideas???? Thanks though...

 

[dyarwood]

That was the same problem I had. Had a link in an email and it was opening in IE. I did the thing I mentioned above and that sorted the problem. If you take out the #_04.2 does that still open the file? I don't think when a spreadsheet opens in IE the VBA code works (that was my problem) So I changed where the file was being opened to Excel and it all worked fine. I would think the problem would lie in where the file is being opened.