Hi guys

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.

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"

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

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