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.