PDA

View Full Version : Excel to open and run Access Macro



Phelony
04-30-2010, 01:43 AM
Hi guys :hi:

It's been a while since I've come to call on your wisdom to aid in a problem, and I regret that this is probably not a very exciting one.

I'm trying to write some code to get a command button in Excel to open Access, run a module within Access and then close it again to update pivot tables held within Excel.

There are hundreds of examples of code across the web and I've found a few on this forum (yes, I did search) but I don't seem to be able to get any of them to work. :help

Currently, I've got this:

Private Sub CommandButton1_Click()
Dim strDatabasePath As String
Dim appAccess As Access.Application

strDatabasePath = "Y:\Access\Database Name 1.mdb"
Set appAccess = New Access.Application
With appAccess
Application.DisplayAlerts = False
.OpenCurrentDatabase strDatabasePath
.DoCmd.RunMacro "macroname"
.Quit
End With
Set appAccess = Nothing

End Sub

I have enabled the Access Object Library within Excel but am still getting the error message "Run-time error 7866 Application-defined or object defined error" :dunno

This is my first time trying to get Excel to operate a "third party" program beyond opening the application.

Could anyone suggest what I'm doing wrong or, should luck have it, happen to have a better piece of code to suggest?

Thanks

Phel :thumb

PS - I'm using Excel 2003....it's not my fault...the company won't upgrade. :(