Macro to run an Existing Microsoft Access Macro

[Paul Dennis]

Hi - I am trying to automate the running of an Access Macro without going
into Access. The access macro using the Transferspreadsheet action.

While the macro below works it has 2 problems:
1 - Since it's in the excel book that the Access macro is trying to update
it waits until the Excel book is closed - hence is it possible to force
Access to update excel that is already open.
2 - The Access macro opens a Db2 database which asks for a username /
pasword, hence is it possible to pass the username / password from the Excel
book from two cells?

The macro in Excel that I am using is:

Sub AccessTest1()
Dim A As Object
Set A = CreateObject("Access.Application")
'A.Visible = False 'I recommend this is remmed to begin with
A.OpenCurrentDatabase ("C:\eESM Reporting\Equinox eESM Web Reporting.mdb")
'update with details of your .mdb file
A.DoCmd.RunMacro "Web Extract" 'update with the name of your Access macro
End Sub

 

[Barb Reinhardt]

Try something like this:

Sub AccessTest1()
Dim AccApp As Access.Application
Set AccApp = CreateObject("Access.Application")
'A.Visible = False 'I recommend this is remmed to begin with
AccApp.OpenCurrentDatabase ("C:\eESM Reporting\Equinox eESM Web
Reporting.mdb")
'update with details of your .mdb file
AccApp.DoCmd.RunMacro "Web Extract" 'update with the name of your Access macro
AccApp.Quit
End Sub

Ensure that in the references, you've chosen the Microsoft Access Object
Library.