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:
- Open a module in Microsoft Access.
- On the Tools menu, click References.
- 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 ACC: Automation Does Not Close Microsoft Excel
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.
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.