Opening a Microsoft Access Report

You can open a Microsoft Access report from Microsoft Excel. The following procedure demonstrates how you can display an existing Access report straight from Excel.

' declare at the top of the module Dim objAccess As Access.Application

Sub DisplayAccessReport() Dim strDb As String Dim strRpt As String strDb = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\Northwind.mdb" strRpt = "Products by Category"

Set objAccess = New Access.Application With objAccess

.OpenCurrentDatabase (strDb) .DoCmd.OpenReport strRpt, acViewPreview .DoCmd.Maximize .Visible = True End With

End Sub

The example procedure below is more versatile, as it allows you to display any Access report in any Access database. Notice that this procedure takes two string arguments: the name of the Access database and the name of the report.

Sub DisplayAccessReport2(strDb As String, strRpt As String)

Set objAccess = New Access.Application With objAccess

.OpenCurrentDatabase (strDb) .DoCmd.OpenReport strRpt, acViewPreview .DoCmd.Maximize .Visible = True End With End Sub

You can run the DisplayAccessReport2 procedure from the Immediate window or from a subroutine, as shown below:

■ Running the DisplayAccessReport2 procedure from the Immediate window:

' Enter the following statement on one line in the Immediate window Call DisplayAccessReport2("C:\Program Files\Microsoft _

Office\Office\Samples\Northwind.mdb", "Sales Totals by Amount")

■ Running the DisplayAccessReport2 procedure from a subroutine:

' Enter the following procedure in the Code window

Sub ShowReport()

Dim strDb As String Dim strRpt As String strDb = InputBox("Enter the name of the database (full path): ") strRpt = InputBox("Enter the name of the report:") Call DisplayAccessReport2(strDb, strRpt) End Sub strDb = InputBox("Enter the name of the database (full path): ") strRpt = InputBox("Enter the name of the report:") Call DisplayAccessReport2(strDb, strRpt) End Sub

Microsoft Access Report
Figure 15-10: A Microsoft Access report can be opened by an Excel VBA procedure.
+1 0

Post a comment