Access Cookbook - Ken Getz [258]
Set objSheet = Nothing
End If
ExitHere:
' Quit and clean up.
obj.Quit
Set obj = Nothing
End Function
In the properties sheet for the command button, enter the value:
=TestExcel( )
in the OnClick event property.
With a module open in design mode, choose the Tools → References... menu item. Choose Microsoft Excel 11.0 Object Library from the list of choices (this item will be on the list if you installed Excel correctly—select the version that you have installed, which may be something besides Excel 11.0). This provides your VBA code with information about the Excel object library, properties, methods, and constants.
Open the form in run mode and click the command button. This will call the TestExcel function and fill the text box with the results.
Discussion
Excel obligingly exposes all of its internal functions to external callers via the Application object. The following sections describe the necessary steps to call Excel functions directly from Access.
TIP
No matter which Excel function you call, the return value will be a variant. Declare a variable as a variant if it will contain the return value from an Excel function. In the examples, the return values went directly to a text box, so you didn't need to select a data type.
Setting up communication with Excel
Before you can call any Excel function, you must start Excel and create an object variable in Access to link the two applications. You'll always use code like this to create this linkage:
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
By linking with Excel's Application object, you can request Excel to evaluate any of its internal functions for you. Creating the object will take a few seconds, as Excel needs to be started. Calling CreateObject will start a new hidden instance of Excel, even if Excel is already running.
You have two other choices. If you know Excel is already running, you can use GetObject to retrieve a reference to an object within Excel or to the Excel Application object. The following code will retrieve a reference to the Application object if Excel is already running:
Set objExcel = GetObject(, "Excel.Application")
If you've set up a reference to Excel using the Tools References... menu item (this is necessary for this example to run), you should be able to use the following code to retrieve a reference to the Excel Application object:
Set objExcel = New Excel.Application
Calling simple Excel functions
Once you've created your Access object that refers to the Excel Application object, you can ask Excel to perform simple calculations for you. For example, to use the Excel Product function, use code like this:
Dim varProd As Variant
varProd = obj.Product(5, 6)
After this call, the variable varProd will contain the value 30.
For example, TestExcel, in frmTestExcel's module, uses the following code fragment to call four Excel functions: Proper, Substitute, Median, and Fact. Each of these functions requires one or more simple parameters and returns a single value. (The AddLine function calls just add the value returned by the function call to the text box on the sample form. These four functions are the first four in the output text box.) The relevant code fragment is:
' String functions
AddLine "Proper:", obj.Proper("this is a test")
AddLine "Substitute:", obj.Substitute("abcdeabcdeabcde", "a", "*")
' Simple math functions
AddLine "Median:", obj.Median(1, 2, 3, 4, 5)
AddLine "Fact:", obj.Fact(10)
Excel supplies many simple functions like these that Access doesn't have. Some of these functions (Proper, for example) are easy enough to replicate in VBA (the StrConv function will convert strings to proper case), but if you already have a connection to Excel, it makes sense to use Excel to retrieve these sorts of values rather than writing the code yourself.
To call analytical or statistical functions in Excel, use the same technique. With the reference to the Excel Application object, call any function that takes simple parameters