Run a Macro anytime the Excel Application is Opened

Cook09
Cook09 used Ask the Experts™
on
Anytime the Excel 2010 application is opened, regardless of what book is selected, I would like to run some code that automatically refreshes a database.  

Yes, if I selected the same workbook, then code could be put in ThisWorkbook.  But, how does one run some code when any workbook is selected or if the Excel Application alone is started?  Can an Addin do this?

The code could actually be simple enough to just open up the Excel Workbook that has all of the code to refresh the database and then it could close itself.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Place code in the Workbook_Open event
Private Sub Workbook_Open()

End Sub

Open in new window

I think putting the workbook open event handler in the personal.xls file will do what you want.
NorieAnalyst Assistant

Commented:
Even if the workbook isn't linked in anyway to the database?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

The macro can open a file which has the data linked.
Most Valuable Expert 2012
Top Expert 2012
Commented:
Barring imnorie's great question, you'd need an Application Event Handler to manage this, and yes, it can be in an add-in.  Now, perhaps that event handler could check the file name being opened, or something, to determine if the database NEEDS refreshing, and exists to be refreshed in that newly opened workbook.  I'll leave it to you to think on.

In the meantime, here's the add-in code.

1. Create a class module called CAppEvent with this code:
Option Explicit

Public WithEvents EventApp As Excel.Application

Private Sub EventApp_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.Name <> ThisWorkbook.Name Then
        MsgBox "Run your macro here"
    End If
End Sub

Open in new window


Then, in a public module, we'll instantiate the class, and add a routine to initialize the application events:
Option Explicit

Dim clsAppEvent As New CAppEvent

Sub InitializeAppEvents()
  Set clsAppEvent.EventApp = Application
End Sub

Open in new window


Then, in ThisWorkbook, you can use this code to get everything going when the add-in is started:
Option Explicit

Private Sub Workbook_Open()
    Call InitializeAppEvents
End Sub

Open in new window


Finally, you can save this file with the name of your choice, but as an add-in (.XLA or .XLAM depending on version, Excel 2003, or 2007+, respectively).

You might also want some menu items for the add-in, to turn it off or to make it exit.  Let me know if you need assistance on that.

See attached example add-in.  Just download it, then run it in Excel.  After that, any workbook that is opened will trigger that application event called: EventApp_WorkbookOpen() and the code there would be the code that you would use to determine if the database refresh is needed, and to refresh your database.

Dave
addInOpenMacro-r1.zip

Author

Commented:
Dave,
Sorry that I haven't responded before now.  Other requirements at work kept me from attempting to figure this out.  However, there was nothing in the .zip file for me to see, as I do have a couple of questions.  I don't know whether it was stripped out via the e-mail security or not.  Would you mind reposting the workbook, and I'll pull it from home?

However, what you seem to be describing is exactly what I'm looking for.  After I write the appropriate macro code, I'll post it and you can critique it for me.

Thanks,

Ron
Most Valuable Expert 2012
Top Expert 2012

Commented:
Whoops!  You are correct - nothing there, lol.  Sorry about that!

Here it is, I tried downloading it and its A-OK.

Dave
addInOpenMacro-r1.zip

Author

Commented:
Dave,
Based on the fact that I just want to open another workbook, preferably in the background if possible, and then it would run its own code located in ThisWorkbook.  I've written a couple of statements. One to just .Open, the other, .Activate is more specific. Does the .Activate set off the ThisWorkbook Event code?

Option Explicit

Public WithEvents EventApp As Excel.Application

Private Sub EventApp_WorkbookOpen(ByVal wkb As Workbook)
Set wkb = "wkbCode.xlsm"
    If wkb.Name <> "wkbCode.xlsm" Then
     'Option 1 to Open
        Workbooks.Open "wkbData.xlsm"
      'Option 2 to Activate
        Workbooks("wkbData.xlsm").Worksheets("SQLData").Activate
    End If
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
It would if you were trapping it, and the code only traps the opening of a workbook, the way you've written it.

Ps - you have a syntax error in your code

Set wkb = "wkbCode.xlsm" is a non-starter

The code is triggered by a Workbook_Open event.  You're already testing the workbook name and if not called "wkbCode.xlsm" then the code proceeds.

However, line 6 needs to come out, as wkb is already "Set" when any workbook is opened.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
May I suggest this code:

Public WithEvents EventApp As Excel.Application

Private Sub EventApp_WorkbookOpen(ByVal wkb As Workbook)

    If wkb.Name <> "wkbCode.xlsm" Then 'if any workbook except wkbCode.xlsm is opened, then
     'Option 1 to Open
        Workbooks.Open "wkbData.xlsm" 'you'll want to fully path this filename - e.g., C:\whatever\wkbData.xlsm
      'Option 2 to Activate
        Workbooks("wkbData.xlsm").Worksheets("SQLData").Activate
    End If
End Sub

Open in new window


Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
And, with Option 2, if the wkbData workbook is already open, or you need to test to see if it is opened, you might modify to:

Public WithEvents EventApp As Excel.Application

Private Sub EventApp_WorkbookOpen(ByVal wkb As Workbook)
Dim wkbData As Workbook

    If wkb.Name <> "wkbCode.xlsm" Then 'if any workbook except wkbCode.xlsm is opened, then
     'Option 1 to Open
        Workbooks.Open "wkbData.xlsm" 'you'll want to fully path this filename - e.g., C:\whatever\wkbData.xlsm
      'Option 2 to Activate
      
        'first, check to see if wkbdata.xlsm is already opened, and if not, open it
        On Error Resume Next
        Set wkbData = Workbooks("wkbdata.xlsm")
        If Err.Number <> 0 Then
            Set wkbData = Workbooks.Open("fully pathed\wkbdata.xlsm")
        End If
        On Error Resume Next
        
        'activate the SQLData worksheet
        wkbData.Worksheets("SQLData").Activate
    End If
End Sub

Open in new window


This assumes with Option 2, you aren't doing Option 1

Cheers,

Dave

Author

Commented:
Dave,
Thanks, I'm working on this at the moment before heading for some "play time" - Holiday.

I have a couple of questions on "Class Modules," but want to read up on them first.  The initial code appears to done, sans "Don't run If" or "Manual Refresh."  If it's saved as a .xlam, and made a part of the toolbar, which I agree that there needs to some mechanism for it not to run, so the code will have to altered a little bit, will it then run automatically when any workbook is open?

I noticed an addin under Custom Toolbars.  Is that some of your code?  If so, it references a "Standard Lists File." What type of file is this, I haven't heard of it before.

Attached is the basics of what you have suggested.

Ron
wkbCode.zip
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok.  that looks like what I gave you.

The mechanism for it not to run?  You just need to test to see if the workbook being opened needs the data refresh or not.  If you can do that, you can run it all the time!  

If you had to start and stop it for when you'd need it or not need it, then why have an add-in at all?  Just run your refresh macro when needed, correct?

>>I noticed an addin under Custom Toolbars.  Is that some of your code?

I don't know.  You've not given me enough information :P

Dave

Author

Commented:
Yes, I agree and will test it out.  The only issue could be if the refresh takes an abnormal amount of time, which I don't see occuring.

Attached is this funny add-in, that I may have loaded at some time, don't recall. Given it's quirky nature would like to disable it, but can't find out how it is being loaded.
DataAdminTemplate-v1-604.zip
Template-Addin.docx
Most Valuable Expert 2012
Top Expert 2012

Commented:
This is certainly not an add-in associated with this thread.

To disable it, select FILE->OPTIONS->Add-Ins->Manage->Excel Add-Ins->Go and search for it there, uncheck it if you find it.

Alternatively, if it is a COM add-in, select FILE->OPTIONS->Add-Ins->Manage->COM Add-ins->Go and uncheck it if you find it there.

Dave

Author

Commented:
Dave,
 After implementating it, and opening it, the Excel code worked liked you had laid it out.

One question: Is there a way of having the Excel book that we are opening, do so "in the background?"  If someone is opening up another workbook and this opens or activates another workbook, would it not become the Active workbook and then have to be minimized so it could finish the refreshing process?  On second thought, I think I saw some code how to have the code minimize the active window, would that be correct?

You mentioned about the application checking to see if the data needs to be refreshed.  Is that done through connecting and looking at the columns and rows in both, or is there some other mechanism that would indicated whether the data needs refreshed or not?

Ron
Most Valuable Expert 2012
Top Expert 2012
Commented:
Yes - we can open that workbook and work with it in the background.

Here's example code:

Option Explicit

Sub runBackground()
Dim oExcel As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWks As Excel.Worksheet

    Set oExcel = CreateObject("Excel.Application")
    Set oWkb = oExcel.Workbooks.Open("your stuff here")
    '...
    '...
    '...
    
    'When done, then close it out
    '
    '
    oWkb.Close savechanges:=True 'or false?
    
    oExcel.Quit
    Set oExcel = Nothing
    Set oWkb = Nothing
    
End Sub

Open in new window



>>You mentioned about the application checking to see if the data needs to be refreshed.  Is that done through connecting and looking at the columns and rows in both, or is there some other mechanism that would indicated whether the data needs refreshed or not?

The comment I made was to see if the workbook being opened needed to have the data refreshed - this is your logic, but it seems to me that you'd not want to refresh on EVERY workbook open - only just those workbooks needing the refresh.

Dave

Author

Commented:
Dave,

Yes, that makes sense.  Thanks for all for help, it made this project a lot easier than I thought it was going to be.

Cheers,
Ron

Author

Commented:
This is why EE has become very valuable to me.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial