• Choose a category
• All
• Classic-Fiction

By Root 1910 0

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.

12.3. Use Excel's Functions from Within Access

Problem

Excel offers an amazing array of statistical, analytical, and financial functions that you'd like to be able to use in Access. You know you can control embedded Excel worksheets, but is there some way to call Excel functions from within Access?

Solution

Access users often ask how they can use Excel functions directly from Access. Using OLE Automation, you can actually request Excel to use its built-in functions to perform calculations and return a value back to your Access application. This requires starting Excel, however, and that can take time, so you wouldn't normally do this for a single calculation. But for a number of calculated values or a single calculation that would be too difficult or time-consuming in Access, it's worth tapping into the connections between Access and Excel.

There are many ways to use Automation to link Excel and Access. You can embed an Excel spreadsheet or chart object into an Access form and control the Excel objects programmatically, as in the example shown in the Solution in Recipe 12.6. You can also use OLE Automation from Access to create and manipulate Excel objects without using an embedded spreadsheet or chart. These methods are detailed in both the Access and Excel manuals. This solution uses the Excel application engine without creating any other