Avatar of Cook09
Cook09
Flag for United States of America asked on

Run a Macro anytime the Excel Application is Opened

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.
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
Cook09

8/22/2022 - Mon
aikimark

Place code in the Workbook_Open event
Private Sub Workbook_Open()

End Sub

Open in new window

Saqib Husain

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

Even if the workbook isn't linked in anyway to the database?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Saqib Husain

The macro can open a file which has the data linked.
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Cook09

ASKER
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
dlmille

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cook09

ASKER
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

dlmille

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
dlmille

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cook09

ASKER
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
dlmille

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
Cook09

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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
Cook09

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cook09

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Cook09

ASKER
This is why EE has become very valuable to me.