Help and Support

Article ID: 153748 - Last Review: January 19, 2007 - Revision: 3.3

ACC: How to Call Excel Functions from Within Microsoft Access

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q153748
Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Access 2000 version of this article, see 198571  (http://support.microsoft.com/kb/198571/EN-US/ ) .

On This Page

Expand all | Collapse all

SUMMARY

This article shows you how to use Automation to call Microsoft Excel functions from within Microsoft Access.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
Back to the top

MORE INFORMATION

Microsoft Excel offers some functions that are not available in Microsoft Access, for example, statistical functions. However, you can access these functions from within Microsoft Access by using Automation if you first set a reference to the Microsoft Excel object library.

To create a reference to the Microsoft Excel object library, follow these steps:
  1. Open a module in Microsoft Access.
  2. On the Tools menu, click References.
  3. In the References box, select the Microsoft Excel Object Library appropriate for your version of Microsoft Excel, and then click OK.

    If you are using Microsoft Excel 5.0 or 7.0, select the Microsoft Excel 5.0 Object Library. If you are using Microsoft Excel 97, select the Microsoft Excel 8.0 Object Library.
After the reference is created, you can use the Object Browser to view all the Microsoft Excel objects, properties, and methods, including the functions available through the Application object.

The following two sample subroutines use Microsoft Excel statistical functions.

NOTE: Microsoft Excel does not automatically close after it has been opened by using Automation. The following subroutines use the Quit method to close Microsoft Excel. For more information about quitting Microsoft Excel, please see the following article in the Microsoft Knowledge Base:
145770  (http://support.microsoft.com/kb/145770/EN-US/ ) ACC: Automation Does Not Close Microsoft Excel
Back to the top

Example 1

The following subroutine uses Automation to call the Microsoft Excel Median() function. Half of the set of numbers fall below and half above the median. 
   Sub xlMedian()
      Dim obj As Excel.Application
      Set obj = CreateObject("Excel.Application")
      MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
      obj.Quit
      Set obj = Nothing
   End Sub
The subroutine displays 6.5 in a message box.

If you are using Microsoft Access 97 with Microsoft Excel 97, you can simplify the code by calling the Microsoft Excel reference directly: 
   Sub xlMedian()
      MsgBox Excel.Application.Median(1, 2, 5, 8, 12, 13)
   End Sub
NOTE: When you use this syntax, Microsoft Excel 97 remains in memory until you reset your code or close your database. Note, too, that although this syntax works with Microsoft Excel 7.0, Microsoft Excel 7.0 remains in memory after you reset your code or close your database, and even after you quit Microsoft Access. To conserve system resources, do not use this syntax with Microsoft Excel 7.0.
Back to the top

Example 2

The following subroutine uses Automation to call the Microsoft Excel ChiInv() function, which returns the inverse or the one-tailed probability of the Chi-Squared distribution: 
   Sub xlChiInv()
      Dim obj As Excel.Application
      Set obj = CreateObject("Excel.Application")
      MsgBox obj.Application.ChiInv(0.05, 10)
      obj.Quit
      Set obj = Nothing
   End Sub
This subroutine displays 18.3070290368475 in a message box.

If you are using Microsoft Access 97 with Microsoft Excel 97, you can simplify the code by calling the Microsoft Excel reference directly: 
   Sub xlChiInv()
      MsgBox Excel.Application.ChiInv(0.05, 10)
   End Sub
Refer to the NOTE in Example 1 for information about the ramifications of using this syntax.

Microsoft Excel also uses add-ins. These are programs that include custom functions and commands. If you need to use a function included in an add-in program, you must first open the add-in. The following is an example of using the LCM (Least Common Multiple) function: 
      Sub xlAddin()
      Dim obj As Excel.Application
      Set obj = CreateObject("Excel.Application")
      ' Opens the add-in, which is in the Analysis folder of the
      ' Microsoft Excel Library Directory.
      obj.workbooks.Open (obj.Application.librarypath & _
       "\Analysis\atpvbaen.xla")
      ' Runs the AutoOpen macro in the add-in
      obj.workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
      MsgBox obj.Application.Run("atpvbaen.xla!lcm", 5, 2)
      obj.Quit
      Set obj = Nothing
   End Sub
This subroutine displays 10 in a message box.
Back to the top

REFERENCES

For more information about using Automation, search for Automation, and then Automation with Microsoft Access using the Microsoft Access 97 Help Index.

For more information about using the Object Browser, search for Object Browser and then "Work with objects in Visual Basic using the Object Browser" using the Microsoft Access 97 Help Index.

For more information about referencing type libraries, search for Type Libraries, and then "Set References to Type Libraries" using the Microsoft Access 97 Help Index.

For more information about add-ins in Microsoft Excel, search for "add-in programs" using the Microsoft Excel 97 Help Index.
Back to the top
APPLIES TO
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbhowto kbprogramming KB153748
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations