Macro 95: Opening an Access Form from Excel
In some instances, you or your clients may need to switch focus to an Access form. This example
demonstrates how you can open an Access form from Excel.
How it works
With this macro, you point Excel to an Access database and trigger a specific Access form to open.
Because you are automating Access, you need to set a reference to the Microsoft Access Object
Library. To do so, open the VBE in Excel and select Tools➜References. The Reference dialog box
activates. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is
your version of Access. Select the check box next to the entry.
Sub Macro95()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase _
(“C:\Temp\YourAccessDatabase.accdb”)
‘Step 3: Open the target form and make Access visible
With AC
.DoCmd.OpenForm “MainForm”, acNormal
.Visible = True
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database application
library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and opens the data-
base that houses the target form.
3. Step 3 opens the appropriate form. The Access form opens in a new Microsoft Access win-
dow. Note that you are not closing the database in the last line of Step 3 (as with the previ-
ous macros). Instead, you make the Access application visible.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click project/workbook name in the Project window
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
