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.22838
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.:banghead:
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.:yes
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:thumb

Cheers