Call Access macro in module from Excel

[harrycosh]

Hi All

I hope I can find some help.

I need to call a macro that is in an access database but I get the error:

Run-time error '2485':

Microsoft Office Access can't find the object 'YourMacroName.'

My code is below that I am currently using:

Public Sub RunAccessMacro()

Dim strDatabasePath As String
Dim appAccess As Access.Application
Dim MacroNameString As String

MacroNameString = "YourMacroName"
strDatabasePath = "c:\Work\Factbase.accdb"

Set appAccess = New Access.Application

With appAccess
Application.DisplayAlerts = True
.OpenCurrentDatabase strDatabasePath
.DoCmd.RunMacro MacroNameString
.Quit
End With

Set appAccess = Nothing

End Sub

I am not sure what I am doing wrong?

Thanks in advance

 

[xenou]

DoCmd.RunMacro will run real Access Macros. If your "macro" is a vba procedure in a standard module, then you want to run it with the Run command:

Application.Run "MyMacroName"

Maybe that will help?
ξ

 

[harrycosh]

Hi xenou

Thanks for your response.

I have a question. The code works fine in Office 2010, (I guess becuase it uses the 14.0 object library). But when I try to use it in 2007 (12.0 object library), I get the error:

Run-time error '40351':

Method 'Run' of object '_Application' failed.

You have been most helpful, is there a solution for this?

Thanks again in advance

 

[Firefly2012]

If you open up the VBE (in Excel 2007) and go Tools menu> References do you have anything listed as MISSING? If so, uncheck and try re-running

 

[harrycosh]

Thanks for the response Firefly2012.

I have checked and there is nothing missing in terms of references.

Help?

 

[Firefly2012]

Did you check Tools>References while the Excel file was open in xl2007 and exhibiting this problem?

 

[xenou]

When you are working across multiple versions (which is always possible, since upgrade happen), better to use late binding:

Dim appAccess As [COLOR="Red"]Object[/COLOR]
[COLOR="Red"]
Set appAccess = CreateObject("Access.Application")[/COLOR]

 

[harrycosh]

Thanks for the response everyone.

I have tried the changes and no luck.

I did however apply the code in Access to the Excel book and it works a treat.

Cheers

 

[CharlesChuckieCharles]

Harry

the original code you wrote worked fine for me

so possibly a path or name missspelling

Public Sub RunAccessMacro()
Dim strDatabasePath As String
Dim appAccess As Access.Application
Dim MacroNameString As String
MacroNameString = "MyMac"
strDatabasePath = "H:\Database1.accdb"
Set appAccess = New Access.Application
With appAccess
Application.DisplayAlerts = True
.OpenCurrentDatabase strDatabasePath
.DoCmd.RunMacro MacroNameString
.Quit
End With
Set appAccess = Nothing
End Sub

MyMac has just a message box saying hello!

if you have spaces in your macro names you may have to use "[]"

 

[harrycosh]

Thanks CharlesChuckieCharles, I have retried the code and still no success. There were no spaces in the name of the code.

I am curious, do I need to call the module something special in Access in order for it to run. This is my first attempt to run anything in Access.

Thanks again.