Access Cookbook - Ken Getz [260]
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)
Calling Excel functions using arrays
Rather than writing to a spreadsheet directly, you might find your work faster if you load a column of data into an array and send it to Excel that way. This avoids multiple Automation calls to Excel (each time you place a value into a cell in Excel, you're going through a lot of internal Automation code). The drawback, of course, is that you're loading all your data into memory. On the other hand, if you're working with so much data that it won't fit into memory, Automation will be too slow to be of much use, anyway!
To copy a column of data to an array, call the acbCopyColumnToArray function (from basExcel in 12-03.MDB), shown in the following code. Pass a variant variable (variants can hold entire arrays in VBA), a table name, and a field name to the function, and it will return the number of rows it placed into the array. This function walks through all the rows in your recordset, copying the values from the specified column into the array:
Public Function acbCopyColumnToArray( _
varArray As Variant, strTable As String, strField As String)
' 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)
rst.MoveLast
ReDim varArray(1 To rst.RecordCount)
rst.MoveFirst
Do While Not rst.EOF
intRows = intRows + 1
varArray(intRows) = rst(strField).Value
rst.MoveNext
Loop
rst.Close
acbCopyColumnToArray = intRows
End Function
Once you've copied the data into arrays, you can call functions in Excel, passing those arrays as if they were ranges. Excel understands that it's receiving multiple values and returns the same results as the tests involving ranges:
' Copy two fields to columns.
Call acbCopyColumnToArray(varCol1, "tblNumbers", "Number1")
Call acbCopyColumnToArray(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 than writing to a spreadsheet. However, if you're working with large volumes of data, you'll want to copy the data to a spreadsheet for Excel to process instead of copying it all into an array.
Closing Excel
Once you're done with your Access/Excel session, you must close the Excel application. If you don't, OLE will continue to start new instances of Excel every time you attempt to connect with Excel.Application (using CreateObject), eating up system resources each time.
To close Excel, use its Quit method:
obj.Quit
Finally, release any memory used by Access in maintaining the link between itself and Excel. The following code releases any memory that the reference to Excel might have been using:
Set obj = Nothing
Comments
Because it takes time to start Excel once you call the CreateObject function, build your applications so that all work with Excel is isolated to as few locations in your code as possible. Another alternative is to make your object variables global; then, you can have your application start Excel if it needs to and leave it open until it's done. Don't forget to close Excel, however, to avoid using up your system memory and resources.
When you're done with the Automation application, you'll need some way of closing down. As with the CreateObject function, each application reacts differently to your attempts to shut it down. You'll need to know how each application you use expects to be