workbook_open() not firing with application.quit

shmick

New Member
Joined
Apr 18, 2013
Messages
4
I hope someone can help me with this one. Here goes.

I am having a problem around the workbook_open event. First let me explain what I am trying to achieve. Basically, I want to start logging the execution of about 30 scheduled tasks which open various workbooks (which perform different operations) into a database. Instead of having to add code to each of these files, I thought creating an add-in which overrides workbook open would be the go.

Basically in my add-in file, I have created a class which overrides workbookopen and workbookbeforeclose. i.e.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim recID As Integer
recID = insertDBStart(Wb)
End Sub
'
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim recID As Integer
recID = updateDBEnd(Wb)
End Sub

Now I have tested this add-in by itself and against vanilla excel documents and it works fine.

Now the schedule tasks files have macros in them which also use the workbook_open() event i.e.

Private Sub Workbook_Open()
Application.Run "'Load Staff.xlsm'!LOAD_Staff"
End Sub

Sub Load_Staff() does a variety of data extracts before closing the opened 'Load Staff.xlsm' workbook using:
Application.Quit
ActiveWorkbook.Close

It appears that this application.quit event prevents the workbook_open event in my add-in from completely firing as once added, I can not get the same functionality from this event any more i.e. it will not log my results into the database (works fine without Application.Quit but we still need that functionality to close the workbooks and excel app once the scheduled task activity is complete).

Any ideas?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Then don't use "Application.quit"
Are you not able to use code to close the workbooks? Doesn't "Application.quit" close excel entirely?
 
Upvote 0
Then don't use "Application.quit"
Are you not able to use code to close the workbooks? Doesn't "Application.quit" close excel entirely?

Thanks for your reply. Application.quit is used to close the workbook and the application. The application is closed so that a new instance of excel can be launched with the next scheduled task.
 
Upvote 0
Thanks for your reply. Application.quit is used to close the workbook and the application. The application is closed so that a new instance of excel can be launched with the next scheduled task.

OK. Had more of a play with it and found it is working on my development environment but not on they production environment. The only differences between the two systems is that on the production environment the Macro Security Settings on my dev box are 'Disable all macros with notification'. i.e. when I load the excel file, once I accept the macro from below the ribbon, it logs fine to the DB.

On the production environment the option 'Enable all macros' is selected. The logging does not work. I am curious as to why the functionality would be different between the two settings? I have tried a few times and can not understand why it is behaving differently.
 
Upvote 0
I'm still having an issue with this. Would love someone if they can to help me out. If I remove the application.quit from the scheduled task document it works fine (problem is the application will not close down). Put the application.quit in and it will not log.

Quite frustrating.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top