Create auto open VBA for ALL excel files opened?

[Jarvoisier]

Thanks in advance for any help on this thread. My question is as follows: Is it possible to create a VBA auto-open macro that will run on ALL excel files, not just one specific file? In my line of work it is necessary to format excel spreadsheets in the same manner. With the hope of streamlining the process of processing files, I would like to use the macro code listed below to automatically run every time any excel file is opened rather than having to manually open the file and then push keystrokes to run the macro.

I have researched on this and other forums and have only been able to automatically run a macro when a specific file opens....yet I have not figured out (or learned for that matter) if it is even feasible to create a macro for what I am requesting.

Many thanks in advance and please see the code below for the macro that I'd like to run automatically. Like I mentioned earlier, I can only get this macro to run on the personal.xlsm file itself, and not on successive excel files opened that I would like to have the macro run on automatically.

Private Sub Workbook_Open()
'
' Jarvis_Header_Footer Macro
' Macro recorded 8/23/2011 by Rob Jarvis
'
'
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = "&F"
.RightHeader = ""
.CenterFooter = "&A"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = True
.PrintTitleColumns = ""
End With
Next ws
End Sub

 

[Tuelor]

You would need to create a general purpose addin, and use a class module with events to capture the event at the application level

Google should be a able to help, but I am working off my phone atm so can't write an example!

 

[Jarvoisier]

Awesome, thanks for that information...I will do a google search and see what I'm able to find.

If by chance you are in front of a computer later and have time to write an example of what you mentioned it will be greatly appreciated!

 

[Kenneth Hobson]

http://support.microsoft.com/kb/213566

 

[Jarvoisier]

Thanks, that looks like the second part that the first person suggested I write. Much appreciated!

 

[Jarvoisier]

So I was able to create the add-in, however I'm not able to get the events that I had setup in the original macro to work inside of the module. Any (additional) ideas?

 

[shg]

You could instead create a template with that setup, and use it as the basis for new workbooks.

 

[Jarvoisier]

The code that I originally listed needs to be run on existing excel files, not new ones. The particular macro has to be run on workbooks prior to us running the excels through a processing software. In a perfect world we'd like the add-in to run when excel opens the workbook vs habing to manually open the workbook then pushing keystrokes to run the macro (is what we currently do). We're trying to make things more efficient (key word trying lol).

Any additional ideas appreciated!

 

[shg]

Hmm.

In the ThisWorkbook module of an add-in,

Option Explicit
 
Public WithEvents appXl As Application
 
Private Sub Workbook_Open()
    Set appXl = Application
End Sub
 
Private Sub appXl_WorkbookOpen(ByVal Wb As Workbook)
    myMacro
End Sub

Then put this in a code module of the same add-in:

Private Sub myMacro()
    Dim wks          As Worksheet
 
    For Each wks In ActiveWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        With wks.PageSetup
            .LeftHeader = ""
            .CenterHeader = "&F"
            .RightHeader = ""
            .CenterFooter = "&A"
            .RightFooter = "Page &P of &N"
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.25)
            .FooterMargin = Application.InchesToPoints(0.25)
            .PrintHeadings = True
            .PrintTitleColumns = ""
        End With
    Next wks
End Sub

 

[Kenneth Hobson]

I made an example using the concept that I linked to earlier.

Insert a class and name it cAOPen.

Option Explicit

'http://support.microsoft.com/kb/213566
Public WithEvents aOpen As Application

Private Sub aOpen_WorkbookOpen(ByVal Wb As Workbook)
  If ActiveWorkbook.Name <> ThisWorkbook.Name Then MsgBox ActiveWorkbook.Name
End Sub

Replace the MsgBox with the call to your macro.

Insert a Module and add:

Option Explicit

Dim autoOpen As New cAOpen

Sub SetAutoOpen()
  Set autoOpen.aOpen = Application
End Sub

Sub UnSetAutoOpen()
  Set autoOpen.aOpen = Nothing
End Sub

Obviously, you would add your Module level code to this Module or another.

In the ThisWorkbook object:

Private Sub Workbook_Open()
  SetAutoOpen
End Sub