Workbook_Open() in personal.xlsb problems - Need correct sub procedure

[sskaff]

First off thanks for all your help over the years. This is the first time I have had to ask a question.

Background:
I updated our company quoting template to always run some code using the sub procedure Workbook_Open() in the ThisWorkbook Object of the file. It runs the code I need whenever the new files are opened.

I have 5000 old workbooks I need to run the same code in every time they are opened. I figured the solution lied in personal.xlsb coding.

Problem:
When I using the sub procedure Workbook_Open() in the ThisWorkbook Object in personal.xlsb it runs for the first file I open but does not run for any subsequent files opened in the same excel application window.

I need to be able to run the same code every time a new file is opened in the same excel application window.

I know this is a simple 1 liner, I just cannot find the answer.

Thanks in advance

 

[Jerry Sullivan]

Hi sskaff,

If you want to run some code each time you open any workbook, you can edit the code below
after adding it to the ThisWorkbook module of your Personal.xlsb file...

Public WithEvents App As Application
 
Private Sub Workbook_Open()
    'runs once per Excel session when Personal.xlsb is opened.
    Set App = Application
    MsgBox "Running Workbook_Open from Personal.xlsb"
End Sub
 
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Running App_WorkbookOpen in " & Wb.Name
    'add code you want to run upon opening each workbook here
   '.....
End Sub

That being said, if you are only needing to run the code once on each of your 5000 old workbooks,
you might be better off using code in a separate workbook that opens each file, runs the code,
then saves the workbook.

That way you aren't running the code in from your Personal.xlsb file
on workbooks that don't need those macros run.

 

[sskaff]

JS411 - That exactly what I was looking for. Thanks for your help. I thought about inserting the code into the old excel 2000 and excel 2003 files but I got scared of my lack of knowledge of:
1) How to open entire folders and sub folders worth of excel files and go through the array of files
2) How to insert a macro to an existing file. We use a certificate to authenticate the macros, that adds additional complexity to making it work

Thanks again.

 

[Jerry Sullivan]

I thought about inserting the code into the old excel 2000 and excel 2003 files but I got scared of my lack of knowledge of:
1) How to open entire folders and sub folders worth of excel files and go through the array of files
2) How to insert a macro to an existing file. We use a certificate to authenticate the macros, that adds additional complexity to making it work

If this is in fact a one-time process, it doesn't have to be that scary.
What's scary is the thought of opening and closing 5000 files manually

The key is to make a backup copy of all your files first and to
test a small sample first to make sure you are getting the desired results.

You can try copying the code below into a standard module of new workbook
and save it to a name like "Batch Processs.xlsm"
Edit the code with just a small sample of the code you want to
apply to all files from your Personal.xlsb file.

When you run the macro from the Batch Processs.xlsm workbook,
you won't be adding any macros to the files you open,
so you shouldn't have any macro security issues.

Sub Process_All_Workbooks_in_Folder()
    Dim strPath As String, strFilename As String
    Dim wbkCurr As Workbook
    strPath = "C:\Test\All Workbooks\" 'edit with your path
    strFilename = Dir(strPath & "*.xls*")
    If strFilename = "" Then
        MsgBox "No files found matching: " _
            & strPath & "*.xls*"
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    While strFilename <> ""
        Set wbkCurr = Workbooks.Open(strPath & strFilename)
        Debug.Print strPath & strFilename
        If Not wbkCurr Is Nothing Then
            With wbkCurr
            '....Add your code here
            
                .Save 'overwrites existing file- make a copy first!                
                .Close
            End With            
        End If
        strFilename = Dir()
    Wend
    Set wbkCurr = Nothing
    Application.EnableEvents = True
End Sub

The most likely problem you will have is getting your macro to work consistently across so many old files.
Things like Protected Sheets, Hidden Sheets, and Merged Cells might cause an error-
but that would happen if you ran this from your Personal.xlsb file too.

If you want an extra set of eyes on to review your code before running your batch,
you can post the "Insert your code here" part on the board.

Good luck!