Using Excel 2007 Functions in Access 2007
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Summary: Learn how to call Microsoft Office Excel 2007 functions from Office Access 2007 by using Automation, and to insert data into Office Excel 2007 sheets or pass arrays of data as parameters. (9 printed pages)
.gif "Office Visual How To")
**Applies to:**2007 Microsoft Office system, Microsoft Office Access 2007, Microsoft Office Excel 2007
Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.
Ken Getz, MCW Technologies, LLC
April 2009
.gif "Overview")
Microsoft Office Access 2007 provides a large number of calculation functions, but not nearly as many as Microsoft Office Excel 2007. If you want to use Office Excel 2007 to perform a statistical, analytical, or financial calculation in an Office Access 2007 application, you could embed a worksheet on an Access form, but if you only need the result of the calculation, you can use Automation to control Excel without ever showing the Excel user interface. |
.gif "See It")
.jpg "Using Excel 2007 Functions in Access 2007 video")
Length: 07:41 | Size: 9.33 MB | Type: WMV file |
Code It | Read It | Explore It
Code It
You can use Excel 2007 functions directly from Access 2007 by using Automation; you send a request to Excel to perform a calculation with a built-in function and then to return a value to your Access application. Of course, you must start Excel to send it a request, and that can take time. Thus, you typically do not use Automation to send a request to Excel from Access unless you have a number of calculations to do, or a single calculation that is too difficult or time-consuming in Access.
There are many ways to use Automation to link Excel and Access. For example, you can embed an Excel spreadsheet or chart object into an Access form and control the Excel objects programmatically. Alternatively, you can use Automation from Access to create and manipulate Excel objects without using an embedded spreadsheet or chart. For more information about either of those two methods, refer to the Excel documentation.
The solution discussed in this article uses the Excel application engine without creating any other specific Excel objects.
To test the communication between Access and Excel, load frmTestExcel from UseExcelFunctions.accdb and then click the Run Excel Tests button on the form. The code attached to the button starts Excel and runs a series of tests, calling Excel to retrieve the results for a number of function calls.
Note that you can run the tests by writing directly to spreadsheet cells to test the multiple-value functions, or by using arrays, which is generally faster, but more complicated. Use the check box on the form to specify how you want to run the tests; each method yields the same results.
When the tests are complete, the sample form will look similar to Figure 1.
Figure 1. Sample form frmTestExcel after the tests run
.jpg "Sample form frmTestExcel after the tests run")
The sample form tests two different types of function calls that you can make to Excel from Access—functions that accept simple parameters, and functions that require multiple values (ranges) as parameters.
The following steps describe how to set up the example form:
Create a new form that contains a single text box (named txtResults on the sample form) and a command button to run the Excel tests.
With a module open in design mode in the VBA editor, click Tools | References from the menu. When the list appears, choose the version of the Microsoft Excel object library that you have installed. (If you installed Excel correctly, you will see the item on the list.) This provides your VBA code with information about the Excel object library, properties, methods, and constants.
Import the module basExcel from UseExcelFunctions.accdb. This module contains a function that copies data from a column in Access to a spreadsheet column in Excel. The module also includes a function that copies data from a column in Access to an array, which Automation can use in place of a range.
Type the following code into the module of the form (click the Build button on the toolbar or choose View | Code).
Private Sub AddLine(strLabel As String, varValue As Variant) Me.txtResults = Me.txtResults & vbCrLf & _ " " & Left(strLabel & Space(20), 20) & varValue DoEvents End Sub Private Function TestExcel() Dim obj As Excel.Application Dim intCount As Integer Dim blnUseArrays As Boolean Me.txtResults = Null blnUseArrays = Nz(Me.chkUseArrays) DoEvents AddLine "Starting Excel:", "Please wait..." ' If you know Excel is open, you could use GetObject(). Set obj = New Excel.Application ' Clear out the results text box. Me.txtResults = Null DoEvents ' 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) ' Analytical functions. AddLine "Kurt:", obj.Kurt(3, 4, 5, 2, 3, 4, 5, 6, 4, 7) AddLine "Skew:", obj.Skew(3, 4, 5, 2, 3, 4, 5, 6, 4, 7) AddLine "VDB:", obj.VDB(2400, 300, 10, 0, 0.875, 1.5) AddLine "SYD:", obj.SYD(30000, 7500, 10, 10) If blnUseArrays Then ' Using arrays. Dim varCol1 As Variant Dim varCol2 As Variant ' Copy two fields to columns. Call CopyColumnToArray(varCol1, "tblNumbers", "Number1") Call CopyColumnToArray(varCol2, "tblNumbers", "Number2") ' Print out calculations based on those ranges. AddLine "SumX2PY2:", obj.SumX2PY2(varCol1, varCol2) AddLine "SumSQ:", obj.SumSQ(varCol1) AddLine "SumProduct:", obj.SumProduct(varCol1, varCol2) AddLine "StDev:", obj.STDEV(varCol1) AddLine "Forecast:", obj.ForeCast(5, varCol1, varCol2) AddLine "Median:", obj.Median(varCol1) Else ' Using ranges. Dim objBook As Workbook Dim objSheet As Worksheet Dim objRange1 As Range Dim objRange2 As Range ' Create the workbook. Set objBook = obj.Workbooks.Add Set objSheet = objBook.WorkSheets(1) ' Copy two fields to columns. intCount = CopyColumnToSheet(objSheet, _ "tblNumbers", "Number1", "A") intCount = CopyColumnToSheet(objSheet, _ "tblNumbers", "Number2", "B") ' Create ranges. Set objRange1 = objSheet.Range("A1:A" & intCount) Set objRange2 = objSheet.Range("B1:B" & intCount) ' Print out calculations based on those ranges. AddLine "SumX2PY2:", obj.SumX2PY2(objRange1, objRange2) AddLine "SumSQ:", obj.SumSQ(objRange1) AddLine "SumProduct:", obj.SumProduct(objRange1, objRange2) AddLine "StDev:", obj.STDEV(objRange1) AddLine "Forecast:", obj.ForeCast(5, objRange1, objRange2) AddLine "Median:", obj.Median(objRange1) ' Convince Excel that it needn't save that ' workbook you created. obj.ActiveWorkbook.Saved = True Set objRange1 = Nothing Set objRange2 = Nothing Set objSheet = Nothing End If ExitHere: ' Quit and clean up. obj.Quit Set obj = Nothing End FunctionIn the properties for the command button, in the OnClick property, enter the following value.
=TestExcel()Open the form in run mode and click the command button to call the TestExcel function and fill the text box with the results.
Excel exposes its internal functions to external callers via its Application object. No matter which Excel function you call, Excel returns a Variant. Declare a variable as a Variant to contain the return value from an Excel function. In the examples, the return values go directly to a text box, so you do not need to select a data type.
Before you can call any Excel function, you must start Excel and create an object variable in Access to link the two applications. You can use the following code to establish the link (if you have set a reference to the Excel object model, there is an easier way to establish the link).
Dim obj As Excel.Application
Set obj = CreateObject("Excel.Application")
By linking with Application object for Excel, you can ask Excel to evaluate any of its internal functions for you. It will take a few seconds to create the object because you must first start Excel. A call to CreateObject starts 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 retrieves a reference to the Application object if Excel is already running.
Set obj = GetObject(, "Excel.Application")
If you set up the reference to Excel in step 2 by using the Tools | References command on the menu, you should be able to use the following code to retrieve a reference to the Excel Application object.
Set obj = New Excel.Application
Once you create an Access object that refers to the Excel Application object, you can ask Excel to perform simple calculations. For example, to call the Excel Product function, use code similar to the following.
Dim varProd As Variant
varProd = obj.Product(5, 6)
After this call, the variable varProd contains the value 30.
TestExcel, in the module frmTestExcel, 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 simply add the value that is 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.)
' 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 does not 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 and returns a single value. The next four examples on the sample form call the Kurt, Skew, VDB, and SYD functions.
' Analytical functions.
AddLine "Kurt:", obj.Kurt(3, 4, 5, 2, 3, 4, 5, 6, 4, 7)
AddLine "Skew:", obj.Skew(3, 4, 5, 2, 3, 4, 5, 6, 4, 7)
AddLine "VDB:", obj.VDB(2400, 300, 10, 0, 0.875, 1.5)
AddLine "SYD:", obj.SYD(30000, 7500, 10, 10)
Sometimes you might need to call Excel functions that require a variable number of values, or you might want to use the data in a table as the input to an Excel function. In these cases, you have two choices. You can call the Excel function by using a spreadsheet range as the input, or you can pass a VBA array directly to the function, which will convert the array and treat it as a built-in range of values. In either case, you'll need a way to get the Access data into the spreadsheet or into an array so that you can use that data as input to the function.
To copy a column of data from an Access table or query into an Excel spreadsheet column, call the CopyColumnToSheet function, which is in the basExcel module in UseExcelFunctions.accdb.
Public Function CopyColumnToSheet( _
objSheet As Excel.Worksheet, _
strTable As String, strField As String, _
column As String) As Integer
' Copy a column from a table to a spreadsheet.
' Place the data from the given field (strField) in
' the given table/query (strField) in the specified
' column (intColumn) in the specified worksheet object
' (objSheet).
' Return the number of items in the column.
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim rng As Excel.Range
Set db = CurrentDb()
Set rst = db.OpenRecordset( _
"SELECT " & strField & " FROM " & strTable)
Set rng = objSheet.Range(column & "1")
rng.CopyFromRecordset rst
rst.Close
CopyColumnToSheet = rng.CurrentRegion.Rows.Count
End Function
Given a reference to an Excel sheet, a table or query name, a field name, and a column for the Excel sheet, CopyColumnToSheet creates a recordset, and uses the Excel CopyFromRecordset function to copy data into the Excel column. The function returns the number of rows that it copied to Excel. For example, to copy the Unit Price field values from the tblProducts table to the first column of the open spreadsheet in Excel, use the following code.
intCount = CopyColumnToSheet(objSheet, "tblProducts", _
"Unit Price", "A")
To keep it simple, this version of the CopyColumnToSheet function does not include error checking, but any code that you use in a working application should check for errors that might occur as you move data from Access to Excel.
After you copy the data to Excel, you can create an object that refers to that range of data as a single entity. Almost every Excel function accepts a range as a parameter if it accepts a group of values as input. For example, the Median function accepts either a list of numbers or a range.
To create a range object in Access, use the Range method in Excel, passing a string that represents the range you want. The following example, used after the form copies the data from a table over to Excel, calculates the median of all the items in the column.
Dim objRange1 As Excel.Range
Set objRange1 = objSheet.Range("A1:A" & intCount)
AddLine "Median:", obj.Median(objRange1)
Some Excel functions require two or more ranges as input. For example, the SumX2PY2 function, which returns the sum of the squares of all the values in two columns (that is, x^2 + y^2), takes two ranges as its parameters. The following code fragment, also from the sample form, copies two columns from tblNumbers to the open sheet in Excel and then performs a number of calculations based on those columns.
' Copy two fields to columns.
intCount = CopyColumnToSheet(objSheet, "tblNumbers", "Number1", "A")
intCount = CopyColumnToSheet(objSheet, "tblNumbers", "Number2", "B")
' Create ranges.
Set objRange1 = objSheet.Range("A1:A" & intCount)
Set objRange2 = objSheet.Range("B1:B" & intCount)
' Print out calculations based on those ranges.
AddLine "SumX2PY2:", obj.SumX2PY2(objRange1, objRange2)
AddLine "SumSQ:", obj.SumSQ(objRange1)
AddLine "SumProduct:", obj.SumProduct(objRange1, objRange2)
AddLine "StDev:", obj.STDEV(objRange1)
AddLine "Forecast:", obj.ForeCast(5, objRange1, objRange2)
AddLine "Median:", obj.Median(objRange1)
Rather than writing to a spreadsheet directly, you might find that your code runs faster if you load a column of data into an array and then send the array to Excel. Sending the array avoids the additional Automation calls to Excel that you would otherwise make each time that you placed a value into a cell in Excel.
To copy a column of data to an array, call the CopyColumnToArray function (from basExcel in UseExcelFunctions.accdb). You pass a Variant variable (variants can hold entire arrays in VBA), a table name, and a field name to the function, and it walks through all the rows in your recordset, copying the values from the specified columns into the array. Then, it returns the number of rows that it placed into the array.
Public Function CopyColumnToArray( _
varArray As Variant, strTable As String, strField As String) _
As Variant
' Copy the data from the given field (strField) of the
' given table/query (strTable) into a dynamic array (varArray).
' Return the number of rows.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intRows As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset(strTable)
' Move to end to get accurate row count.
rst.MoveLast
rst.MoveFirst
ReDim varArray(1 To rst.RecordCount)
Do While Not rst.EOF
intRows = intRows + 1
varArray(intRows) = rst(strField).Value
rst.MoveNext
Loop
rst.Close
CopyColumnToArray = intRows
End Function
Once you have copied the data into arrays, you can call functions in Excel, passing those arrays as if they were ranges. Excel understands that it is receiving multiple values and returns the same results as the tests that use ranges.
' Using arrays.
Dim varCol1 As Variant
Dim varCol2 As Variant
' Copy two fields to columns.
Call CopyColumnToArray(varCol1, "tblNumbers", "Number1")
Call CopyColumnToArray(varCol2, "tblNumbers", "Number2")
' Print out calculations based on those ranges.
AddLine "SumX2PY2:", obj.SumX2PY2(varCol1, varCol2)
AddLine "SumSQ:", obj.SumSQ(varCol1)
AddLine "SumProduct:", obj.SumProduct(varCol1, varCol2)
AddLine "StDev:", obj.STDEV(varCol1)
AddLine "Forecast:", obj.ForeCast(5, varCol1, varCol2)
AddLine "Median:", obj.Median(varCol1)
This method is both simpler, and faster at run time, than writing directly to a spreadsheet.
Once you're done with your Access/Excel session, you must close Excel. If you don't, Automation will continue to start a new instance of Excel each time that you attempt to connect with Excel.Application, and use system resources each time. To close Excel, use its Quit method.
obj.Quit
Finally, release any memory that Access is using to maintain its link with Excel.
Set obj = Nothing
Read It
Because it takes time to start Excel once you create an instance of the Excel Application object, you should try to isolate any work with Excel in your application code to as few locations as possible. Alternatively, make your object variables global so that your application can start Excel if it needs to and leave it open until it's done.
Don't forget to close Excel to prevent it from wasting your system memory and resources. Excel will not quit unless you explicitly order it to by using its Application.Quit method. If you just set the Excel.Application object variable to the value Nothing without executing the Quit action, the hidden copy of Excel will continue to run, wasting memory and resources.
You can use Automation to expose the rich and varied inner workings within Excel, but it is nearly impossible to take advantage of those capabilities without reference materials. The solution in this article is just a hint of what is available to you in Access from Excel. If you need to use the two products together, use the Object Browser in the Visual Basic Editor to explore the objects in the Excel object model. You can also view the Help topic for each object from within the Object Browser.