workbook_open() not firing with application.quit

[shmick]

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?

 

[davesexcel]

Then don't use "Application.quit"
Are you not able to use code to close the workbooks? Doesn't "Application.quit" close excel entirely?

 

[shmick]

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.

 

[shmick]

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.

 

[shmick]

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.