Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

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 data—you 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 using—if 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.
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.
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 Excel—you can have any Automation client (including Access itself) start up a new copy of Access to accomplish Access tasks from that host.

[ Team LiB ]