Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 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.

2. 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

3. 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

4. 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.

5. 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.

   Make sure that all these assumptions are met. You could code around
   all three of these, but these reflect the way the example was set up.

6. Add a
   command button to your worksheet. Place the following code in its
   OnClick event:

   Private Sub cmdAccess_Click( )
   Call HandleAccessReport
   End Sub

7. 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.