Fail to recall a Excel macro after auto open from Outlook macro

Question

I wrote a macro from Outlook to open Excel.

Below the code:

Public Sub OpenMasterPM()
    Dim xExcelFile As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    Dim xExcelRange As Excel.Range
    xExcelFile = "C:\Users\andrea.vighetti\Documents\Banca5\Evolutive\Master PM_prova.xlsm"
    Set xExcelApp = CreateObject("Excel.Application")
    Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
    Set xWs = xWb.Sheets(1)
    xWs.Activate
    xExcelApp.Visible = True     
End Sub

After that, Excel is open and I have an Auto_Open macro on Excel that I want start when that page is open but this doesn't work.

It seems that auto open macro wrote on Excel doesn't start when Excel is open starting from the macro of Outlook, below the vba code on Excel:

Sub Auto_open()

    Sheets("Evolutive TFS").Select
    ActiveWorkbook.RefreshAll
    Sheets("Gantt").Select
    ActiveSheet.ListObjects("Gantt").Range.AutoFilter Field:=3, Criteria1:="=Implementation", Operator:=xlOr, Criteria2:="=Test"

End Sub

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-02-18T14:45:30

Instead of Auto_open use the Workbook.Open Event because Auto_open is obsolete.
Put it into ThisWorkbook not into a module!

You might benefit from reading How to avoid using Select in Excel VBA.

Private Sub Workbook_Open()
    Sheets("Evolutive TFS").RefreshAll
    Sheets("Gantt").ListObjects("Gantt").Range.AutoFilter Field:=3, Criteria1:="=Implementation", Operator:=xlOr, Criteria2:="=Test"
End Sub

Also make sure the macros are enabled in Excel's Trust Center either for the file location or that the file is digitally signed. Otherwise the Excel security might block the macros from execution.

See Enable or disable macros in Office files.

Fail to recall a Excel macro after auto open from Outlook macro

1 Answers