Peter,
Automation help for manipulating Excel from Access at MsKb:
302460 Office XP Products
260410 Office 2000 Products
From the Graph10.chm help file:
Chart types that support error bars:
You can add error bars to data series in 2-D area, bar, column, line, xy
(scatter), and bubble charts. For xy (scatter) and bubble charts, you can
display error bars for the x values, the y values, or both.
Add error bars to a data series:
Click the data series to which you want to add error bars.
On the Format menu, click Selected Data Series.
On the X Error Bars tab or the Y Error Bars tab, select the options you
want.
Note: If you change the datasheet values associated with the data points
in the series, the error bars are adjusted to reflect your changes.
From VBAGR10.chm:
Using the ErrorBars Object
Use the ErrorBars property to return the ErrorBars object. The following
example turns on error bars for series one in myChart and then sets the end
style for the error bars.
myChart.SeriesCollection(1).HasErrorBars = True
myChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
ErrorBar Method:
Applies error bars to the specified series. Variant.
expression.ErrorBar(Direction, Include, Type, Amount, MinusValues)
expression Required. An expression that returns one of the objects in the
Applies To list.
Direction: Required XlErrorBarDirection. The error bar direction.
XlErrorBarDirection can be one of these XlErrorBarDirection constants.
xlX Can only be used with scatter charts.
xlY default.
Include: Required XlErrorBarInclude. The error bar parts to be included.
XlErrorBarInclude can be one of these XlErrorBarInclude constants.
xlErrorBarIncludeBoth default.
xlErrorBarIncludeMinusValues
xlErrorBarIncludeNone
xlErrorBarIncludePlusValues
Type: Required XlErrorBarType. The error bar type.
XlErrorBarType can be one of these XlErrorBarType constants.
xlErrorBarTypeCustom
xlErrorBarTypeFixedValue
xlErrorBarTypePercent
xlErrorBarTypeStDev
xlErrorBarTypeStError
Amount: Optional Variant. The error amount. Used for only the positive
error amount when Type is xlErrorBarTypeCustom.
MinusValues: Optional Variant. The negative error amount when Type is
xlErrorBarTypeCustom.
Example
This example applies standard error bars in the Y direction for series one.
The error bars are applied in the positive and negative directions. The
example should be run on a 2-D line chart.
myChart.SeriesCollection(1).ErrorBar _
Direction:=xlY, Include:=xlErrorBarIncludeBoth, _
Type:=xlErrorBarTypeStError
End of Error Bar info, on to Excel formulas...
Most of the "fancy" Excel formulas are available for use in Access via
Automation or referencing an Excel library, examples below:
Public Sub XIRRTest()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
Dim p(4) As Double
p(0) = -10000
p(1) = 2750
p(2) = 4250
p(3) = 3250
p(4) = 2750
Dim d(4) As Date
d(0) = DateValue("1/1/1998")
d(1) = DateValue("3/1/1998")
d(2) = DateValue("10/30/1998")
d(3) = DateValue("2/15/1999")
d(4) = DateValue("4/1/1999")
Debug.Print objExcel.Application.XIRR(p, d)
objExcel.Quit
Set objExcel = Nothing
End Sub
Public Function fXIRR(ByRef arFlows, ByRef arFlowdates, Optional dblGuess As
Double = 0.100000001490116) As Double
'With a reference set to Office Web Components - msowcf.dll
'All of the nifty functions available in Excel are available
Dim oFunc As MSOWCFLib.OCATP
Set oFunc = New MSOWCFLib.OCATP
fXIRR = oFunc.XIRR(arFlows, arFlowdates, 0.05)
Set oFunc = Nothing
End Function
Now if you want to do the calculations in Excel, one technique would be to
use the Range CopyFromRecordset method shown below to fill a datarange in an
Excel worksheet.
Public Sub CopyRs2Sheet(strSql As String, strWorkBook As String, _
Optional strWorkSheet As String, Optional strCellRef
As String)
'Uses the Excel CopyFromRecordset method
'strSql: Sql Select string
'strWorkBook: Full path and name to target wb, will create if doesn't
exist
'strWorkSheet: Name of target worksheet, will create if doesn't exist
'strCellRef: Upper Left cell for data, defaults to A1
On Error GoTo ProcError
DoCmd.Hourglass True
Dim objXLApp As Object 'Excel.Application
Dim objXLWb As Object 'Excel.Workbook
Dim objXLSheet As Object 'Excel.Worksheet
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
Dim iSheets As Integer
'set rs from sql, table or query
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
'start Excel
Set objXLApp = CreateObject("Excel.Application")
'open workbook, error routine will
'create it if doesn't exist
'only create workbooks with 1 sheet
iSheets = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = iSheets 'restore user's setting
'select a worksheet, if sheet doesn't exist
'the error routine will add it
If strWorkSheet = "" Then
strWorkSheet = "Sheet1"
End If
'If Range is missing default to A1
If strCellRef = "" Then
strCellRef = "A1"
End If
'select desired worksheet
Set objXLSheet = objXLWb.Worksheets(strWorkSheet)
'insert recordset into Excel Worksheet using CopyFromRecordset method
objXLSheet.Range(strCellRef).CopyFromRecordset rs
objXLSheet.Columns.AutoFit
'Save wb
objXLWb.Save
objXLWb.Close
'close up other rs objects
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set objXLSheet = Nothing
Set objXLWb = Nothing
'quit Excel
If Not objXLApp Is Nothing Then objXLApp.Quit
Set objXLApp = Nothing
DoCmd.Hourglass False
Exit Sub
ProcError:
Select Case Err
Case 9 'Worksheet doesn't exist
objXLWb.Worksheets.Add
Set objXLSheet = objXLWb.ActiveSheet
objXLSheet.NAME = strWorkSheet
Resume Next
Case 1004 'Workbook doesn't exist, make it
objXLApp.Workbooks.Add
Set objXLWb = objXLApp.ActiveWorkbook
objXLWb.SaveAs strWorkBook
Resume Next
Case Else
DoCmd.Hourglass False
MsgBox Err.Number & " " & Err.Description
Stop
Resume 0
End Select
End Sub
Hope it helps...
Steve
