Automatically Run Workbook Event on Workbook Open

[ilcaa]

I have this macro stored in my personal.xlsb folder and it is stored in "This Workbook".

Everytime a workbook is open a want to run a SUB() that checks the name of the workbook and does something depending on certain workbook names....

i am trying this small macro just to test it and It doesnt run automatically once a workbook opens.

Private Sub Workbook_Open()
If ActiveWorkbook.Name = "indeed" Then
msgBox (ActiveWorkbook.Name)
Else
MsgBox (ActiveWorkbook.Name)
End If
End Sub


what am i doing wrong to have this run automatically everytime a workbook is opened? thanks

 

[Joe4]

That code will only run on the workbook it is stored in (which is personal.xlsb). Event procedures are only triggered to run by events in the workbooks they are stored in.

So if you would want this to run on the other workbooks, I think it would need to be stored in each of those workbooks.

What exactly is it that you are trying to accomplish with this? If we know where you are trying to go with this, we may have some suggestions on other ideas to accomplish what you want.

 

[ilcaa]

thanks, but I do web extraction of data and the program stores the title of the extraction and the date as an excel spreadsheet. So when I open these workbooks I want it to automatically run a formatting macro I have. problem is there are 6 different formatting macros depending on which extraction is run that day, (part of the workbook name). So i wanted to store 6 different formatting macros and depending on what the title is, it would run the appropriate formatting macro. i thought open events would work on any workbook that is opened...


So its impossible for me to store it in the workbook since It is not created yet, hope that makes sense. What do you recommend?

 

[Joe4]

i thought open events would work on any workbook that is opened...

No, they only pertain to the workbooks that they are physically stored in.

The first question to consider is this:

- Are the formats of these different types of files really that different that it requires 6 different formatting macros, or can it be combined down to one (if they are pretty similar minus a few details, you can probably add conditional statments to handle the differences)?

 

[ilcaa]

each has its own column heading, each has its own columns sizing, etc. I tried to do it as easy as possible and each is independent of the others, different industries, etc

 

[Joe4]

If the structure of the files is really dramatically different, then I might approach it like this:

- Create one large macro file where you stored each macro is separate module.
- Create a "main" macro that basically looks at the file name, and based on what it is, calls the appropriate macro to run against it.

Then what I would probably do it download all files to some sort of "holding download location", and then have this macro process against all files in that folder, and move them out when finished. You could even schedule that macro to run automatically at set times via a Scheduler program (like Windows Scheduler).

 

[ilcaa]

i can have my extraction program run Window Shell commands, this is where you are going?

Is there is a windows shell command for "Folder Changes" and could Windows Shell run excel Macros?


thanks for the help

 

[Joe4]

I really don't use Window Shell commands at all, so I can't help you there. But I could something like just with Excel VBA and Windows Scheduler (which comes standard with most Windows installations).

I would just have the Excel Macro file have Workbook_Open code which would kick it off to run whenever opened, and then use Windows Scheduler to open at pre-determined dates and times.

Whenever run, the VBA would look at the files in your pre-determined Download folder and format and move each one accordingly.

 

[ilcaa]

understand, but i think the biggest problem is that I cannot store the macro in the workbook prior.... since the extraction is run then saved as an excel file and excel wont run open events on workbooks that do not have the macro stored it its own "this workbook"


so i dont know how i could get excel to run once it is opened.. that, to me, is the problem. How to fire up a macro once a random workbook is opened and allow it to get for conditions..(workbook name in this case)

 

[Jaafar Tribak]

What you actually need is to trap the Application events not the Workbook events:

Place this in the ThisWorkbook Module of your Personal workbook:

Private WithEvents AppEvents As Application
 
Private Sub Workbook_Open()
 
    Set AppEvents = Application
 
End Sub
 
Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    If Not Wb Is Me Then
        Call YourMacro(Wb)
    End If
 
End Sub
 
Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Not Wb Is Me Then
        Call YourMacro(Wb)
    End If
 
End Sub

Private Sub YourMacro(Wb As Workbook)
 
    If Wb.Name = "indeed" Then
        MsgBox (Wb.Name)
    Else
        MsgBox (Wb.Name)
    End If
 
End Sub