There are two methods to do this
Method 1. Auto open macro in VBA Project Module
In this case you add your macro code to VBA project module and it gets executed when you open the file. When you go to your VBA editor, you select a module as highlighted in the picture and copy following code to have auto run macro.
Private Sub Auto_Open()
MsgBox "This is auto open macro in Module"
Method 2. Auto open macro in ThisWorkbook Section
In this method you add your code to Thisworkbook Section of your excel file. double click on highlighted potion in your VBA editor and paste following code
Private Sub Workbook_Open()
MsgBox "This is auto open macro in This workbook"
You can replace the message box line with your code / action you want your macro to do. I have used this as example to keep my macro code short for better understanding.