Error 438 when setting min and max in two vertical chart axis

[sabael]

Hello
I have an Access Database to export a query to Excel and create a chart from query range B and range C.qry_chart_without_minmx_vertical_axis_values.jpg
My code works well:

Sub cmdTransfer_Click()Dim sExcelWB As String
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object


Set xl = CreateObject("excel.application")
sExcelWB = "D:\testing2\" & "qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")
'Set ch = xl.Charts.Add
Set ch = ws.Shapes.AddChart.Chart


With ch
    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLinemarkers
    .ChartGroups(1).GapWidth = 69
End With
xl.Visible = True
xl.UserControl = True
End Sub

However, I need the vertical axes to show the scale between the minimum value and the maximum value as shown in the query.

Looking for such min and max values I have been tested this code:

With ch    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLineMarkers
    .ChartGroups(1).GapWidth = 69


    myMax = DMax("Total_Sal", "qry_task")
    myMin = DMin("Total_Sal", "qry_task")
    With .SeriesCollection(1)
        With .Axes(xlvalue, xlPrimary)
            .MinimumScale = myMin
            .MaximumScale = myMax
        End With
    End With
    
    myMax = DMax("Task_Val", "qry_task")
    myMin = DMin("Task_Val", "qry_task")
    With .SeriesCollection(2)
        With .Axes(xlvalue, xlSecondary)
            .MinimumScale = myMin
            .MaximumScale = myMax
        End With
    End With


End With

But VBA displays in line ".MinimumScale = myMin", ERROR, 438 Object does not support this property or method.
I really appreciate any help to fix such Error.

[PhilS]

Maybe the actual error is happening on the line before.

With .SeriesCollection(2)
        With .Axes(xlvalue, xlSecondary)

From what I can see, a Series does not have an Axes collection property. That would rather be a property of the Chart itself.

[sabael]

Now my code works fine.
I deleted 'With ch .chartTyoe = xlColimnClustered to:
Instead, I am using:
With ch
.ChartType = xlColumnClustered
....
....

and the rest of the code lines are the same.

Many thanks for your reply

Cheers