CODEWITHSUNDEEP
vbaexcel
Jim D
Jim D

Reputation: 31

Is there a way to have a VBA script that runs every time an Excel file opens?

What I'm looking for is having the auto_open() function run on every excel file that is created. Is there a way that this can be implemented?

Upvotes: 0

Views: 673

Answers (2)

Gilligan
Gilligan

Reputation: 176

  1. Type ?Application.StartupPath in the immediate window to find your XLSTART folder.
  2. Inside this folder, there will be a template file named Book.xlt or something similar.
  3. Open that file and put your Auto_Open() event inside that template, then save it as a template in the XLSTART Folder. Be sure it actually saves in the correct place and overwrites the original template. NOTE: You may have to change the template file type to .xltm and delete the original .xlt version since it will have macros in it.
  4. Your Auto_Open() event should now be in every new file you create.

Automatically open a workbook template or worksheet template when you start Excel

Upvotes: 1

Rory
Rory

Reputation: 34075

You'll need a separate workbook that contains the code you want to run - for example, your Personal Macro workbook. In the Thisworkbook module of that workbook, add this:

Private WithEvents appExcel As Excel.Application
Private Sub Workbook_Open()
   Set appExcel = Application
End Sub

Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)
    Call Macro1
End Sub

where Macro1 is the code you want to run for each workbook.

Upvotes: 1

Related Questions