Executing Microsoft Access Autoexec macro when opening Microsoft Excel file

Question

I have an autoexec macro in a microsoft access database that I would like to execute everytime I open an excel file. Essentially, I need to open the access database to trigger the autoexec and then close again.

I have some VBA code in my excel file already to date/timestamp individual records (worksheet code) but I was hoping there is some functionality in excel that I can utilize when the file is opened to trigger this access process.

Can anyone share some code they have used for this type of functionality or share any excel functionality that I'm not aware of to accomplish this same action?

Thanks in advance!

Answer 1

Executing a macro from Excel using VBA is pretty simple. You can use the following VBA to open an Access database, and then close it again.

With CreateObject("Access.Application")
    .OpenCurrentDatabase "path\to\my\file.accdb"
    'AutoExec macro should fire automatically
    .Quit
End With

Answer 2

To run an Access script when Access opens, make sure you have a FUNCTION (not a sub) in a standard Module.

Option Compare Database

'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err

    DoCmd.RunCommand acCmdWindowHide
    MsgBox "Welcome to the client billing application!", vbOKOnly, "Welcome"
    DoCmd.OpenTable "Orders", acViewNormal, acEdit


AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function

Keep in mind, there are several ways to control Access from Excel. Here is one option.

Global oApp As Object

Sub OpenAccess()

   Dim LPath As String
   Dim LCategoryID As Long

   'Path to Access database
   LPath = "C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb"

   'Open Access and make visible
   Set oApp = CreateObject("Access.Application")
   oApp.Visible = True

   'Open Access database as defined by LPath variable
   oApp.OpenCurrentDatabase LPath

   'Open form of interest
   oApp.DoCmd.OpenForm "Form1"

End Sub