Macro 94: Opening an Access Report from Excel
Access reports allow you to build professional looking reports that have a clean PDF-style look and
feel. If you run and distribute a great deal of Access reports, the following macro can help automate
your processes.
How it works
This macro demonstrates how you can open your Access reports right from Excel. The appealing
thing about this technique is that you don’t see Access at all; the report goes straight to a Word rich
text file.
Note that you will 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 opens. Scroll down until you
find the entry Microsoft Access XX Object Library, where XX is your version of Access. Select the check
box next to the entry.
Sub Macro94()
‘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 report as a Word rich text file
With AC
.DoCmd.OpenReport “Revenue Report”, acViewPreview
.DoCmd.RunCommand acCmdOutputToRTF
.Quit
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database
application library.
2. In Step 2, the AC variable starts a new instance of Microsoft Access and opens the
database that houses the target report.
3. Step 3 simply runs the appropriate report, sending the output to a Microsoft Word rich text
file. After the file is output, the database closes.
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.
