Call an Access Macro from Within Excel 1

[DanAuber]

Can anyone tell me the code to run an Access Macro from within Excel (via an Excel Macro)

let's say that:
The Access Database is C:\Accessdb.mdb and the macro is runmac

Thanks for the help

Dan

 

[RobCPA]

Try this:

Shell("msaccess c:\accessdb.mdb /xRunMac")

This worked for me based on a test I did on my computer. You may need to add the full path for msaccess. Also, This will open an instance of Access in order to run the macro, so you'll probably want to add a Quit command to the macro to give control back to Excel.

 

[AnotherHiggins]

I use this:

Dim objAcc As Access.Application
Set objAcc = New Access.Application
With objAcc
    .OpenCurrentDatabase "C:\Accessdb.mdb"
    .DoCmd.RunMacro ("runmac")
    .CloseCurrentDatabase
    .Quit
End With
Set objAcc = Nothing

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

By the way: for future reference, please post VBA/Macro-specific questions in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DanAuber]

Sorry anotherhiggins, your code returns the following error:

"Compile error

User-defined type not defined"

can I get round this ?

 

[AnotherHiggins]

Sure. In the VB Editor, go to Tools > References and check the box beside Microsoft Access X.X Object Library (the X.X represents whatever version number you have).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.