Access Cookbook - Ken Getz [255]
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.
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