Open Access using Excel and run Macro

[dstilson]

Hello,

I have a excel macro set up to launch Access using the Shell command and then I want to run a macro in Access. This works fine under office 2003, but does not work under 2007. I get and error "Object Required" on the DoCmd.RunMacro "mcrShipReq". Here is what I have:

Dim appTraffic

Application.DisplayAlerts = False
Application.ScreenUpdating = False

appTraffic = Shell("c:\Program Files\Microsoft Office\Office12\msaccess.exe D:\Data\ProdDataTraffic.accdb", 2)

AppActivate appTraffic 'Open Database ProdDataTraffic.accdb

DoCmd.RunMacro "mcrShipReq"

DoCmd.Quit acQuitSaveAll 'Quit Microsoft Access Application

' Return control to column E1 and refresh Data from MPD2

Range("E1").Select
ActiveWorkbook.RefreshAll

Thanks in advance for you help

 

[PHV]

You have to instantiate an Access.Application object as DoCmd is a method of this object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dstilson]

Thanks PHV

I changed the code as follows.

Dim appTraffic As Access.Application

Set appTraffic = CreateObject("Access.Application")

appTraffic.Visible = False 'Run database in background

appTraffic.OpenCurrentDatabase "D:\Data\ProdDataTraffic.accdb" 'Open ProdDatTraffic database

DoCmd.RunMacro "mcrShipReq" 'Run Macro to create Ship Required Tables

appTraffic.Quit 'Close "Microsoft Access 2007"

' Return control to column E1 and refresh Data from MPD2

Range("E1").Select
ActiveWorkbook.RefreshAll

I also added Microsoft Access object Library in References

Thanks again

 

[PHV]

I'd replace this:
DoCmd.RunMacro "mcrShipReq"
with this:
appTraffic.DoCmd.RunMacro "mcrShipReq"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886