Run Access Macro from Excel

[bradyboyy88]

Hi,

I have seen numerous posts of this done but I keep running into the error Method 'Run' of object '_Application' failed. The code in access just has the following:

Public Sub TestMacro()
msgbox "test"
End Sub

Then in excel I use the following:

Public Sub TrackerAutomation()
    
    If Not FileInUse(DatabaseDirectory) Then
        Dim AccDB As Access.Application
        Set AccDB = CreateObject("Access.Application")
        
        On Error GoTo ErrorHandler
        
        AccDB.OpenCurrentDatabase DatabaseDirectory, True


        AccDB.Visible = False


         AccDB.Run "testMacro"


        AccDB.Quit
    Else
       
    End If
    
ErrorHandler:


    Debug.Print Err.Description
    AccDB.Quit
End Sub

I have the reference for Access 15.0 library as well. One thing that is strange when we were testing this run command it seemed to work on a 64bit laptop which had windows 10 . THat laptop was actually used to make the access DB and macro. It originally had object library 16.0 referenced but since windows 8.1 doesnt have that I disabled that and made 15.0.

Thanks
Shaun

 

[=ODIN=]

In order to rule out whether it is a library reference issue, try late binding the access app.

Just change your one line of code:

[COLOR=#333333] [/COLOR][COLOR=#333333]Dim AccDB As Access.Application[/COLOR]

to

[COLOR=#333333]Dim AccDB As object[/COLOR]