1

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

YowE3K
  • 23,852
  • 7
  • 26
  • 40
icecat
  • 13
  • 4

1 Answers1

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
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks a lot for your reply!! I have tried to use your code but I get the error 429: "ActiveX Component can't create object". So I guess I met your problem... How to open the Excel worksheet? And what do you mean by saying: This needs to be the Excel application that is running your embedded worksheet. If it's another, it will fail. – icecat Jul 13 '17 at 06:28
  • See the edited answer. By that last comment I mean, if you have multiple open Excel applications, the code can bind the wrong one, and then it won't work. I can provide some code to either generate an error message if Excel is running, or close all open Excel applications. – Erik A Jul 13 '17 at 06:39
  • Just need to add the line: Me.xlObject.Action = acOLEActivate and it works. Thank you for your time! – icecat Jul 13 '17 at 06:51
  • I can provide some code to either generate an error message if Excel is running, or close all open Excel applications. => I am very interested in these 2 actions as well! – icecat Jul 13 '17 at 06:53
  • THANKS A LOT FOR YOUR EFFORTS!!! I have been looking for a solution for the last 2 days, you saved my life! Thanks again! – icecat Jul 13 '17 at 07:18