Run Access Macro from Excel

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
554
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:

Code:
Public Sub TestMacro()
msgbox "test"
End Sub
Then in excel I use the following:

Code:
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
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:
Code:
[COLOR=#333333] [/COLOR][COLOR=#333333]Dim AccDB As Access.Application[/COLOR]
to

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

Watch MrExcel Video

Forum statistics

Threads
1,102,844
Messages
5,489,221
Members
407,681
Latest member
HoneyBadger914

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top