Access 2010 Excel Automation VBA Chart Error Bars Help

Posted on 2012-09-17
Last Modified: 2013-09-03
I am looking for some help with creating excel charts via VBA in MS Access.  The porblem is this:  I can not format the error bars in a line graph once I have created the chart in Excel using my VBA code.  If I do this manually using excel I can change the error bars in the manner I would like.  The end goal is to change the error bars from the default value to ±1 standard deviation in the Y axis.

The code is below.  I have added comments.  About 3/4 of the way down you will see the line that is not working.  I have searched numerous places, reformatted the data so I can be viewed as X/Y chart and have had no success.  Any and all help is appreciated.

Thank you,


Here is the code as it stands now:

Private Sub cmdExporttoExcel_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim ChartRange
Dim n As Integer
Dim m As Integer
Dim SavePath
Dim TestNum
Dim SC As Integer
m = 1
'***************Set Path Create Instance of Excel, New Workbook and Save Workbook********

SavePath = "C:\Temp\"
TestNum = Forms!Form1!cmb_test
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = False
    Set xlWB = xlApp.Workbooks.Add
    xlWB.SaveAs SavePath & TestNum & ".xlsx"
    n = xlWB.Sheets.Count
    Set xlWB = Nothing
  '***************Export Data********************  
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_AVGCOF_Data_Mean", SavePath & TestNum & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_AVGCOF_Data_SD", SavePath & TestNum & ".xlsx"

'*****************Make Excel Visible and Reopen after Data Export, Delete Unused Sheets***************
xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Open(SavePath & TestNum & ".xlsx")
    Do Until m = n + 1
        m = m + 1
    m = 1

'***************Activate Worksheet, Select Data and Create Line Chart***************
Set xlWS = xlWB.Worksheets(1)
    With xlWS
    .Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select
    .Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select
    Set xlChart = xlWS.ChartObjects.Add(50, 40, 300, 200).Chart
    xlChart.ChartType = xlLineMarkers
    xlChart.HasTitle = True
    xlChart.ChartTitle.Caption = "Coefficient of Friction"
    xlChart.SetSourceData xlApp.Selection
    xlChart.PlotBy = xlColumns
    xlChart.Axes(xlCategory).HasTitle = True
    xlChart.Axes(xlCategory).AxisTitle.Caption = "Day"
    xlChart.Axes(xlValue).HasTitle = True
    xlChart.Axes(xlValue).AxisTitle.Caption = "COF"
    SC = xlChart.SeriesCollection.Count
    xlChart.SeriesCollection(m).HasErrorBars = True
'***********************The line below generates a run time error 1004.  This is the only line that does not work.  The error bars are created without problem and selected without problem when I step through the code.  For whatever reason, I can not change the type*************************
xlChart.SeriesCollection(m).ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeStDev
'*****************************everything from here on out works fine.*******************
m = m + 1
    Loop Until m = SC
    xlChart.Location xlLocationAsNewSheet
    Set xlApp = Nothing
    Set xlWB = Nothing
End Sub
Question by:doyletim
    LVL 14

    Accepted Solution

    It appears to me that this error comes up because you have left out the Amount parameter to the ErrorBar method. While it is formally an optional parameter (it can be omitted if a different Type is specified, it evidently is needed when Type:=xlErrorBarTypeStDev.

    xlChart.SeriesCollection(m).ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeStDev, Amount:=1

    Open in new window

    to replace your problem line. You might also consider removing the previous two lines that add the default error bars and select them.

    Hope this helps,

    Author Comment

    Thank you for the reply.  I will try this and accept as a solution as soon as I give it a shot.

    LVL 14

    Expert Comment

    Any luck adding the  parameter Amount:=1 to the code?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now