打开Microsoft Excel文件时执行Microsoft Access Autoexec宏

使用VBA从Excel执行宏非常简单。可以使用以下VBA打开Access数据库,然后再次将其关闭。

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

要在Access打开时运行Access脚本,请确保在标准模块中有一个函数(而不是sub)。

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

请记住,有几种方法可以控制Excel的访问。这里有一个选择。

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