Run a Macro anytime the Excel Application is Opened

Place code in the Workbook_Open event

Private Sub Workbook_Open()

End Sub

Select allOpen in new window

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

Even if the workbook isn't linked in anyway to the database?

The macro can open a file which has the data linked.

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

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

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

Select allOpen in new window

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

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

Select allOpen in new window

Dave

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

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

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

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

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

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

This is why EE has become very valuable to me.