Link to home
Start Free TrialLog in
Avatar of Ange
Ange

asked on

Code to run Access macro from Excel

I need to run a set of Access macros from an Excel sheet. Trying to get just one to work for a start. I found some code which looks like it should work. The code should open Access, open the specified db, run a specified macro, close the db. It opens Access ok but then puts up a warning "can't find mac_xxx" without apparently even opening the db. The db and macro names match what's in the code. Any ideas? I'm using Access 2013. I think the code may have been written for a previous version. Code below.

Sub Run_Access_Macro()

         'Opens Microsoft Access and the file Fred.accdb
            Shell ("C:\Program Files (x86)\Microsoft Office\Office15\msaccess.exe C:\Documents\Fred.accdb")
         'Initiates a DDE channel to Microsoft Access
            Chan = DDEInitiate("MSACCESS", "system")
         'Activates Microsoft Access
             Application.ActivateMicrosoftApp xlMicrosoftAccess
         'Runs the macro "mac_XXX" from the Fred.accdb file
             Application.DDEExecute Chan, "mac_XXX"
         'Terminates the DDE channel
          Application.DDETerminate Chan

      End Sub
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Dim A As Object
Set a = CreateObject("Access.Application")
'//Use next statment only needed if you want to see Access - default is to be "invisible"
a.Visible = False
a.OpenCurrentDatabase ("C:\Documents\Fred.accdb")
a.DoCmd.RunMacro "mac_XXX"
a.CloseCurrentDatabase
a.Quit
Set a = Nothing

Open in new window

Regards
Avatar of Ange

ASKER

Hi,
I tried this code. I get "Run time error 2501 - the Run Macro action was cancelled."

Debug highlights A.Do.Cmd.RunMacro "mac_xxx"

Any ideas?
Hi,

why not transform the macro in VBA

and use

a.Application.Run "mac_XXX"
instead of
a.DoCmd.RunMacro "mac_XXX"
Avatar of Ange

ASKER

Hi
I don't understand or know how to "transform the macro in VBA".

I changed the code as above but hit another error, 2517 "Access can't find the procedure "mac_xxx".

?
Could you send a screenshot of your macro
ASKER CERTIFIED SOLUTION
Avatar of Ange
Ange

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.