Access Cookbook

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 Section 12.6.2 . 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 specific Excel objects.

To test the OLE communication between Access and Excel, load frmTestExcel from 12-03.MDB and click the button on the form to start the ...