In order to run macro on open file event you need to have the VBA code (macro) inside the Workbook_Open() event in ThisWorkbook element on Project Explorer.
You must first open the VBA editor in Excel and type your macro code inside the Sub Workbook_Open().
I attach a picture for you to see what I am trying to explain above:
In this way, when the file open event happens, the macro code inside Workbook_Open sub will run.
Don’t forget to save the file as .xlsm or the older format .xls, as .xlsx doesn’t save the code behinf the excel file.
Enjoy Excel VBA Macros!