How does one create an Excel Clustered Column-Line Combo Chart with VBA?

Justin Swanson 1 Reputation point
2021-08-05T19:45:34.977+00:00

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

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,595 questions
{count} votes