ACC2000: How to Call Excel Functions from Within Microsoft Access

ID: Q198571

The information in this article applies to:

Microsoft Access 2000

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

Advanced: Requires expert coding, interoperability, and multiuser skills.

SUMMARY

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

MORE INFORMATION

Microsoft Excel has some functions that Microsoft Access does not have, for example, statistical functions and add-in functions. By using Automation, you can use these Excel functions in Access. To do so, first set a reference to the Microsoft Excel object library.

To set a reference to the Microsoft Excel object library, follow these steps:

In Access, press ALT+F11 to switch to the Visual Basic Editor.

On the Tools menu, click References.

In the References box, click the Microsoft Excel 9.0 Object Library check box, and then click OK.

After you set the reference, 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 procedures use Microsoft Excel statistical functions.

NOTE: Excel does not automatically close after you open it through Automation. The following sample procedures use the Quit method to close Excel. For additional information about quitting Excel, please see the following article in the Microsoft Knowledge Base:

Q210129 ACC2000: Applications Run from Automation Do Not Always Close

Example 1

The following function uses Automation to call the Excel Median() function. Half of the set of numbers fall below and half above the median.


Sub xlMedian()
   Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")
   MsgBox objExcel.Application.Median(1, 2, 5, 8, 12, 13)
   objExcel.Quit
   Set objExcel = Nothing
End Sub

The function displays 6.5 in a message box.

Example 2

The following function uses Automation to call the Excel ChiInv() function, which returns the inverse, or the one-tailed probability, of the Chi-Squared distribution:


Sub xlChiInv()
   Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")
   MsgBox objExcel.Application.ChiInv(0.05, 10)
   objExcel.Quit
   Set objExcel = Nothing
End Sub

The function displays 18.3070290368475 in a message box.

You can simplify the code by calling the Excel reference directly:


   Sub xlChiInv()
      MsgBox Excel.Application.ChiInv(0.05, 10)
   End Sub

NOTE: When you use this syntax, Excel remains in memory until you reset the code or close the database.

Using Add-ins

Excel also uses add-ins. These programs include custom functions and commands. If you need to use a function included in an add-in program, first open the add-in. The following example uses the LCM (Least Common Multiple) function:


Sub xlAddin()
   Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")

   ' Opens the add-in, which is in the Analysis folder of the
   ' Excel Library Directory.
   objExcel.workbooks.Open (objExcel.Application.librarypath & _
      "\Analysis\atpvbaen.xla")

   ' Runs the AutoOpen macro in the add-in
   objExcel.workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
   MsgBox objExcel.Application.Run("atpvbaen.xla!lcm", 5, 2)
   objExcel.Quit
   Set objExcel = Nothing
End Sub

The function displays 10 in a message box.

REFERENCES

For more information about Automation, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type automation in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Object Browser, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type browser in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about referencing type libraries, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type set references in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbdta AccCon IntpOlea
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo