Run an Access macro from Excel? 1

[AirZebra]

Hi all,

I have the following code (for which I apologise most sincerely) in an Excel spreadsheet that compacts an Access database to a separate location and then opens it. Is it possible to then run a macro stored in the database from the code in Excel?

Dim StandardDBM As Database
Dim NewDBM As Database

StandardDBM = "D:\Exported Data\Reports\Standard\ManStandard.mdb"
NewDBM = "D:\Exported Data\Reports\" & LastMonthDate & "\ManReport - " & LastMonthDate & ".mdb"

DBEngine.CompactDatabase StandardDBM, NewDBM
Dim MyDBM As Object
Set MyDBM = GetObject(NewDBM)
MyDBM.Application.Visible = True
Set MyDBM = Nothing[\code]

The macro that I want to run is called mcrFinalise.

Regards,

Alan

 

[bkclaw113]

First make certain that you reference the Microsoft Access Object Library in your reference Library, then try something like this:

Public Sub runACMacro()

Dim acApp As Access.Application

Dim StandardDBM As String
Dim NewDBM As String

Set acApp = New Access.Application

StandardDBM = "D:\Exported Data\Reports\Standard\ManStandard.mdb"
NewDBM = "D:\Exported Data\Reports\" & LastMonthDate & "\ManReport - " & LastMonthDate & ".mdb"

DBEngine.CompactDatabase StandardDBM, NewDBM

acApp.OpenCurrentDatabase (NewDBM)

acApp.DoCmd.RunMacro mcrFinalise
acApp.Quit

set acApp = Nothing
End Sub

 

[AirZebra]

Thanks BKClaw,

When I add your code and run the macro I get

Run Time Error 91

Object variable or With block variable not set[\code]

at the
acApp.OpenCurrentDatabase (NewDBM)
line.  Do you have any idea why this could be?

Regards,

Alan

 

[AirZebra]

Sorry - forget that last reply, it just meant that I didn't have Access open. Now I have Access open it comes up with a run time error 2501 saying something about how I used the runmacro command and then clicked cancel in a dialogue box. Now I know it is Friday afternoon, but I am pretty sure that no boxes appeared on my screen, and I am almost definite that I didn't click on anything that wasn't there.

 

[AirZebra]

I finally got that last bit sorted out. For any interested parties it seems the database was closing before it got a chance to run the macro. I am awarding BKClaw a star as his reply helped me a lot.

Regards,

Alan