如何通过Access VBA创建Excel簇状柱形与折线组合图表

整合思路 #

原示例代码提供了从Access导出查询数据到Excel的完整流程，宏录制代码提供了自定义组合图表各系列类型、坐标轴分组的核心逻辑，整合时只需替换原示例中的图表配置段，同时修复原示例的语法错误即可。

整合后完整代码 #

Option Compare Database
' 自定义配置项：修改为你实际使用的查询名、工作表名
Private Const conQuery = "qryTopTenProducts"
Private Const conSheetName = "Top 10 Products"

Private Sub Command201_Click()
    Dim rst As ADODB.Recordset
    ' Excel对象变量
    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
    
    ' 初始化Excel应用对象
    Set xlApp = New Excel.Application
    ' 新建工作簿
    Set xlBook = xlApp.Workbooks.Add
    
    ' 保留1张工作表，删除多余表
    xlApp.DisplayAlerts = False
    For i = xlBook.Worksheets.Count To 2 Step -1
        xlBook.Worksheets(i).Delete
    Next i
    xlApp.DisplayAlerts = True
    
    ' 绑定工作表、修改表名
    Set xlSheet = xlBook.ActiveSheet
    xlSheet.Name = conSheetName
    
    ' 读取Access查询数据
    Set rst = New ADODB.Recordset
    rst.Open _
        Source:=conQuery, _
        ActiveConnection:=CurrentProject.Connection
    
    ' 写入数据到Excel
    With xlSheet
        ' 写入表头并加粗
        For i = 0 To rst.Fields.Count - 1
            With .Cells(1, i + 1)
                .Value = rst.Fields(i).Name
                .Font.Bold = True
            End With
        Next i
        ' 批量写入查询结果
        .Range("A2").CopyFromRecordset rst
        ' 自动适配列宽
        .Columns.AutoFit
        ' 可根据需要自定义数值列格式，示例：第二列设为整数格式
        .Columns(2).NumberFormat = "#,##0"
    End With
    
    ' 创建图表
    Set xlChart = xlApp.Charts.Add
    With xlChart
        .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
        .PlotBy = xlColumns
        .Location _
            Where:=xlLocationAsObject, _
            Name:=conSheetName
    End With
    
    ' 配置组合图表参数（此处为宏录制逻辑的适配改造）
    With xlBook.ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = conSheetName & " Chart"
        .ChartTitle.Font.Size = 16
        ' 以下为自定义每个系列的图表类型、坐标轴分组，可根据你的实际需求调整
        .FullSeriesCollection(1).ChartType = xlColumnClustered
        .FullSeriesCollection(1).AxisGroup = 1
        .FullSeriesCollection(2).ChartType = xlColumnClustered
        .FullSeriesCollection(2).AxisGroup = 1
        .FullSeriesCollection(3).ChartType = xlLine
        .FullSeriesCollection(3).AxisGroup = 1
        .FullSeriesCollection(4).ChartType = xlColumnClustered
        .FullSeriesCollection(4).AxisGroup = 2
        .FullSeriesCollection(5).ChartType = xlLine
        .FullSeriesCollection(5).AxisGroup = 1
        ' 其他样式配置
        .Axes(xlCategory).TickLabels.Font.Size = 8
        .Axes(xlValue, xlPrimary).TickLabels.Font.Size = 8
        .Axes(xlValue, xlSecondary).TickLabels.Font.Size = 8
    End With
    
    ' 显示Excel窗口
    xlApp.Visible = True

ExitHere:
    On Error Resume Next
    ' 释放对象
    rst.Close
    Set rst = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Sub

HandleErr:
    MsgBox Err.Number & ": " & Err.Description, , "生成Excel图表出错"
    Resume ExitHere
End Sub

注意事项 #

• 需提前在Access VBA编辑器的「工具-引用」中勾选Microsoft Excel xx.x Object Library和Microsoft ActiveX Data Objects xx.x Library，否则会报编译错误
• 代码中FullSeriesCollection的索引对应Excel表中从第二列开始的数值列，可根据你实际的字段数量、展示需求调整每个系列的图表类型和所属坐标轴
• 若不需要次坐标轴，删除对应AxisGroup = 2的配置即可

内容的提问来源于stack exchange，提问作者jswanson1001