Recipe 12.2 Print an Access Report from Excel
12.2.1 Problem
You keep and work with your data
in Excel, but you'd like to print reports using
Access. You know you can use the Access Report Wizard directly from
Excel, but you'd like more control over the process.
Can you do this using VBA?
12.2.2 Solution
Access allows you to control its actions
using Automation. Anything you can do directly from Access, you can
also do from Excel. This solution uses Automation to link your Excel
worksheet to an Access database, use that data as the data source for
a report, and then remove the linked table. Because you can directly
link to an Excel worksheet from Access, this process
doesn't need to involve importing the datayou
can use it as-is, live, in your Excel environment.To try out the sample database, first load
12-02.XLS into Excel. This workbook includes the
data (shown in Figure 12-3) and the VBA code that
controls the sample. Next, click the Open Access Report button, which
causes Excel to load a copy of Access and then load
12-02.MDB , link the current data to that
database, and display the report in print preview mode.
Figure 12-3. Use data in Excel to print a report in Access
To use this technique in your own applications, follow these steps:
- Create a database, including a report that you'd
like to print. You may want to link the Excel data
that's going to be the data source now, so that
it's easier to create the report. You can leave it
linked (in which case you'll want to modify the
example code in your spreadsheet to not relink the table) or you can
delete the link once you've created the report. - In Excel, create a new workbook or use an
existing one. Add a new module (choose Tools Macro Visual Basic
Editor, and then Insert Module) and enter the following code (or copy
it from 12-02.XLS ):Option Explicit
Const conXLS = "12-02.xls"
Const conMDB = "12-02.mdb"
Const conTableName = "CustomersXLS"
Const conReportName = "Customers"
Private Sub HandleAccessReport( )
' This sample assumes that the database and
' the spreadsheet are in the same directory.
' It doesn't HAVE to be that way, of course,
' but it makes this simple example much simpler.
Dim accApp As Access.Application
Dim strPath As String
Dim strDatabase As String
Dim strXLS As String
On Error GoTo HandleErr
' Get the location of the files.
strPath = FixPath(ActiveWorkbook.Path)
strDatabase = strPath & conMDB
strXLS = strPath & conXLS
' Launch a new instance of Access.
Set accApp = New Access.Application
' Open the database.
With accApp
.OpenCurrentDatabase filepath:=strDatabase, Exclusive:=True
' Link the spreadsheet to Access.
With .DoCmd
.TransferSpreadsheet _
TransferType:=acLink, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:=conTableName, _
Filename:=strXLS, _
HasFieldNames:=True
' Open the report in preview mode.
.OpenReport conReportName, acViewPreview
' Delete the attached table.
.DeleteObject acTable, conTableName
End With
End With
ExitHere:
Set accApp = Nothing
Exit Sub
HandleErr:
MsgBox Err & ": " & Err.Description, , _
"Error in HandleAccessReport"
Resume ExitHere
End Sub
Private Function FixPath(strPath As String) As String
If Right(strPath, 1) = "\" Then
FixPath = strPath
Else
FixPath = strPath & "\"
End If
End Function - Choose the Tools References... menu item and, from the
list of references, check the Microsoft Access 11.0 Object Library
item, as shown in Figure 12-4. (Select the object
library corresponding to the version of Access that
you're usingif you're using
Office 2002, for example, select Access 10.0 Object Library in this
dialog box.) This will add an explicit reference to the Access type
library to your project, making Access's object
model and constants available to your code.
Figure 12-4. Use the References dialog to set a reference to Access in Excel
- In the code you've
just entered, modify the constants conXLS and
conMDB to match the names of your spreadsheet and
database, respectively. Also modify the
conTableName and conReportName
constants to match the data source for your report (its RecordSource
property) and the name of the report itself. - The example code expects three conditions to be true:
- The spreadsheet and the database are in the same directory.
- The spreadsheet data includes the field names in the first row.
- The path that contains the files is not the drive's
root directory.
all three of these, but these reflect the way the example was set up. - Add a
command button to your worksheet. Place the following code in its
OnClick event:Private Sub cmdAccess_Click( )
Call HandleAccessReport
End Sub - Save your spreadsheet. When you click the button
you've created, it will start Access, link the
table, print the report, delete the link, close the database, and
quit Access.
12.2.3 Discussion
This example uses Automation to control Access directly from Excel.
The process of printing the report can be broken down into four
steps:
- Get the reference to Access and open the database.
- Link the Excel worksheet to the database.
- Print the report.
- Clean up.
The next few paragraphs discuss these items. The HandleAccessReport
procedure in Step 2 includes all the code for this process.To retrieve a reference to Access, you
can use the Access Application object. The line of code that does the
work looks like this:
Dim accApp As Access.Application
Set accApp = New Access.Application
To
open the database, use the OpenCurrentDatabase method of the
Application object:
With AccApp
.OpenCurrentDatabase filepath:=strDatabase, Exclusive:=True
Access provides three methods that work with the current database
from Automation:
- OpenCurrentDatabase
(not to be confused with the DAO method,
OpenDatabase) opens a database in the Access user
interface. If a database is already open, you'll get
a runtime error. - CloseCurrentDatabase
closes the current database. This method will generate a runtime
error if there's no current database. - NewCurrentDatabase
creates a new database altogether. Once you've done
this, you can use OLE Automation to create all the objects you need
in that database as well.
In addition to these three
methods, the Access Application object provides two useful
properties: UserControl and
Visible. The UserControl property returns
True if you opened Access under your own power, or
False if Automation started Access. The property
is read-only and lets your code work differently depending on how the
database was loaded. The Visible property allows you to control
whether an instance of Access started via Automation is visible or
not. If UserControl is True,
you cannot change the Visible property. If
UserControl is False, the
default value for Visible is False, but you can
set it to be True with code like this:
' Set the Application's Visible property to True
' if OLE Automation initiated the session.
With accApp
If Not .UserControl Then
.Visible = True
End If
End With
To link the Excel spreadsheet to the
Access database, use the TransferSpreadsheet method of the DoCmd
object. This method allows you to import or link a spreadsheet to the
database, depending on the parameters you set. In this example, the
code specifies that the spreadsheet is of type
acSpreadsheetTypeExcel9 (this applies to Excel
2000 and later), includes field names in the top row, and is to be
linked, not imported:
With .DoCmd
.TransferSpreadsheet _
TransferType:=acLink, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:=conTableName, _
Filename:=strXLS, _
HasFieldNames:=True
Once you've executed the TransferSpreadsheet method,
your database will include an attached table, with the name stored in
strTableName, that retrieves data from the
spreadsheet whose name is in strXLS.To print the report, use the OpenReport
method of the DoCmd object, as shown in the following code fragment,
which opens the report in print preview mode using the
acViewPreview constant:
.OpenReport conReportName, acViewPreview
If you want the report to be sent directly to the printer, use the
acViewNormal constant.To clean up once your
report has finished printing, the code first deletes the linked
table, then closes the database, and finally shuts down the instance
of Access that it initiated. To delete the table, it uses the
DeleteObject method of the DoCmd object. To close the current
database, it uses the CloseCurrentDatabase method of the Application
object. Finally, to shut down Access, it uses the Quit method of the
Application object. The cleanup code is:
With DoCmd
' Do all the work here...
.DeleteObject acTable, strTableName
End With
' This isn't necessary, but it's neat.
.CloseCurrentDatabase
' Quit Access now.
.Quit
End With
Set obj = Nothing
You aren't limited to running Access from
Excelyou can have any Automation client (including Access
itself) start up a new copy of Access to accomplish Access tasks from
that host.