Run a macro with embedded Excel sheet in Access

Question

My problem is quite simple but I haven't found a solution so far...

I created a form in Access called Form1. In this form, I inserted an "Unbound Object Frame", which is a new macro-enabled Excel worksheet. In the Excel sheet, I create a macro we can call "ExcelMacro".

I want to know how to run macros in this Excel sheet from Access, e.g. in my Access macro, run "ExcelMacro"

I do NOT want to link my Excel sheet to an external Excel workbook.

Do you have any ideas?

Thanks a lot for your help!

Edit:

In case you need some additional information:

Name of Unbound Object Frame: xlObject

OLE Class: Microsoft Excel Macro-Enabled 12

Class: Excel.SheetMacroEnabled.12

Sheet name: Sheet1

Answer 1

You can use the following in Access:

Public Sub RunExcelMacro()
    Dim excelApp As Object
    Set excelApp = GetObject(, "Excel.Application")
    excelApp.Run "HelloWorld"
End Sub

Where "HelloWorld" is the name of the Excel Macro.

GetObject(, "Excel.Application") gets the latest opened Excel application. This needs to be the Excel application that is running your embedded worksheet. If it's another, it will fail.

Also, the worksheet needs to be open, else it will fail (you can add either of the following code segments to open it before running this).

Me.MyOLEUnbound.Verb = 0 'vbOLEPrimary
Me.MyOLEUnbound.Action = 7 

or

Me.MyOLEUnbound.AutoActivate = 1 'vbOLEActivateGetFocus
Me.MyOLEUnbound.SetFocus

To make sure there are no other instances of Excel running, and possibly quit them if they are (note: these make use of runtime errors and error handlers)

Public Function IsExcelRunning() As Boolean
    IsExcelRunning = True
    On Error GoTo ReturnFalse:
    Dim obj As Object
    Set obj = GetObject(, "Excel.Application")
    Exit Function
ReturnFalse:
    IsExcelRunning = False
End Function

Public Sub CloseAllExcel()
    Dim obj As Object
    On Error GoTo ExitSub
    Dim i As Integer
    'There shouldn't be more than 10000 running Excel applications
    'Can use While True too, but small risk of infinite loop
    For i = 0 To 10000
        Set obj = GetObject(, "Excel.Application")
        obj.Quit
    Next i
ExitSub:
End Sub