• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1007
  • Last Modified:

Access 2010 Excel Automation VBA Chart Error Bars Help

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,

Tim

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
    xlWB.Close
    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
        xlWB.Worksheets(1).Delete
        m = m + 1
    Loop
    m = 1

'***************Activate Worksheet, Select Data and Create Line Chart***************
Set xlWS = xlWB.Worksheets(1)
    xlWS.Activate
    With xlWS
    .Range("A1").Select
    .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
   
    Do
    xlChart.SeriesCollection(m).HasErrorBars = True
    xlChart.SeriesCollection(m).ErrorBars.Select
'***********************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
    xlWS.Activate
     
    Set xlApp = Nothing
    Set xlWB = Nothing
   
End Sub
0
doyletim
Asked:
doyletim
  • 2
1 Solution
 
pteranodon72Commented:
doyletim,
 
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,
pT72
0
 
doyletimAuthor Commented:
Thank you for the reply.  I will try this and accept as a solution as soon as I give it a shot.

Tim
0
 
pteranodon72Commented:
Any luck adding the  parameter Amount:=1 to the code?
pT2
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now