The example below demonstrates how we can run a Macro Access from another application or database using theOLE Automation.
We perform the following steps:
- let's create two Access databases calling them TestDB.mdb and TestDB2.mdb
- in the first database TestDB.mdb we create a macro calling it TestMsg with the following action: "Test Macro" Message Window
- in the second database TestDB2.mdb we create another macro calling it TestMsg with the following action: Message Window: "Test Macro2"
- in the application you will use, choose from the references Microsoft Access Object Library (from version 8.0 onwards)
Let's create the following Sub:
Dim objACC As New Access.Application
Set objACC = GetObject("C:TestDB.mdb") 'open first database objACC.DoCmd.RunMacro ("TestMsg") 'run macro objACC.Quit Set objACC = GetObject("C:TestDB2.mdb") 'open second database objACC.DoCmd.RunMacro ("TestMsg") 'run macro objACC.Quit Set objACC = Nothing
In case you get an Error like this: "The RunMacro action was canceled. Error 2501"Or"The RunMacro action was canceled".
The error is due to the existence of an Exit Macro or Stop Macro declaration. If you notice that, even in the presence of the error, the macro has completed its activity, you can very well use theOn Error Resume Next at the beginning of Sub.
Tested on Office 2003 and Office 2007.