No existing macros availble when Excel is opened from Outlook macro

Question

I have a macro in Outlook, and I have it opening an Excel file that is saved on my desktop. Once the file is open, I would like to run a macro that I have written in excel, but none of my excel macros are available. The macros are available whenever I open excel any other way, and macros are enabled when I open excel through outlook vba. My question is, how do I make these macros available when I open Excel via the Outlook macro? Please reference my code below.

'Pre:None
'Post:Excel will have been opened, and the macro "CreatePowerPoint()"
'     will have been run on the excel document
Sub Gimba()
    Dim xlApp As Object, xlWkb As Object
    'open excel
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True ' can be False if you do not wont see reaction,
                     ' byt make sure is not fail
    'Do not show any alerts
    xlApp.DisplayAlerts = False

    'open excel document
    Set xlWkb = xlApp.Workbooks.Open(file path goes here)

    'call macro on excel document
    Call xlApp.Run("CreatePowerPoint")
End Sub

steveo40 steveo40 · Accepted Answer · 2013-05-09T14:14:56

I assume that the macros that you have normally available are stored in your personal.xls workbook? If so, then you just need to load that up before you try and launch your CreatePowerPoint macro.

Try something like (depends on where your personal workbook is stored):

xlApp.Workbooks.Open ("C:\Documents and Settings\YourUserNameHere\Application Data\Microsoft\Excel\XLSTART\personal.xlsb")

As an aside, you might find it easier to write the VBA code if you use early binding. To do this, you need to add a reference to the Excel object model, then instead of using CreateObject, you could use Set xlApp = new Excel.Application. That way you get all the nice Intellitype assistance.

No existing macros availble when Excel is opened from Outlook macro

1 Answers