How does one create an Excel Clustered Column-Line Combo Chart with VBA?
I am working on creating code to create an Excel Combo Chart-Clustered Column-Line Chart, but cannot find the proper enumeration for this chart type, can someone help? I see it nowhere in the list of chart enumerations...https://learn.microsoft.com/en-us/office/vba/api/excel.xlcharttype
I am currently working with the current sample code, but need this specific chart type:
Private Sub Command201_Click()
Option Compare Database
Private Const conQuery = "qryTopTenProducts"
Private Const conSheetName = "Top 10 Products"
Private Sub Command201_Click()
Dim rst As ADODB.Recordset
' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlChart As Excel.Chart
Dim i As Integer
On Error GoTo HandleErr
' Create Excel Application object.
Set xlApp = New Excel.Application
' Create a new workbook.
Set xlBook = xlApp.Workbooks.Add
' Get rid of all but one worksheet.
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True
' Capture reference to first worksheet.
Set xlSheet = xlBook.ActiveSheet
' Change the worksheet name.
xlSheet.Name = conSheetName
' Create recordset.
Set rst = New ADODB.Recordset
rst.OPEN _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection
With xlSheet
' Copy field names to Excel.
' Bold the column headings.
With .Cells(1, 1)
.Value = rst.Fields(0).Name
.Font.Bold = True
End With
With .Cells(1, 2)
.Value = rst.Fields(1).Name
.Font.Bold = True
End With
' Copy all the data from the recordset
' into the spreadsheet.
.Range("A2").CopyFromRecordset rst
' Format the data.
.Columns(1).AutoFit
With .Columns(2)
.NumberFormat = "#,##0"
.AutoFit
End With
End With
' Create the chart.
Set xlChart = xlApp.Charts.Add
With xlChart
.ChartType = xlComboColumnClusteredLine
.SetSourceData xlSheet.Cells(1, 1).CurrentRegion
.PlotBy = xlColumns
.Location _
Where:=xlLocationAsObject, _
Name:=conSheetName
End With
' Setting the location loses the reference, so you
' must retrieve a new reference to the chart.
With xlBook.ActiveChart
.HasTitle = True
.HasLegend = False
With .ChartTitle
.Characters.Text = conSheetName & " Chart"
.Font.Size = 16
.Shadow = True
.Border.LineStyle = xlSolid
End With
With .ChartGroups(1)
.GapWidth = 20
.VaryByCategories = True
End With
.Axes(xlCategory).TickLabels.Font.Size = 8
.Axes(xlCategoryScale).TickLabels.Font.Size = 8
End With
' Display the Excel chart.
xlApp.Visible = True
ExitHere:
On Error Resume Next
' Clean up.
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in CreateExcelChart"
Resume ExitHere
End Sub