Access调用Excel的CreateGraph宏生成图表时报‘下标越界’错误求助

错误原因 #

出现「下标越界」的核心原因是代码依赖活动对象（如ActiveWorkbook、Sheets("Summary")）且存在不必要的对象重赋值，同时可能存在路径格式问题，导致无法正确定位目标工作表：

• 打开目标工作簿后，错误地将xlWB重新赋值为ActiveWorkbook，在Access调用Excel的场景下，ActiveWorkbook未必指向刚打开的文件，后续引用工作表时会找不到目标。
• 直接使用Sheets("Summary")、ActiveSheet这类未明确归属对象的调用，容易因Excel上下文切换（比如其他工作簿激活）导致找不到指定工作表。
• 文件路径末尾存在多余空格"X:\path_here "，可能导致拼接后的路径无效，无法打开正确的Excel文件，进而触发工作表找不到的错误。

修正后的代码 #

Sub CreateGraph()
    Dim xlApp As Object 'Excel.Application
    Dim xlWB As Object  'Excel.Workbook
    Dim xlSh As Object  'Excel.Worksheet
    Dim xlChart As Object 'Excel.ChartObject
    
    ' 创建Excel应用实例并打开指定工作簿
    Set xlApp = CreateObject("Excel.Application")
    ' 移除路径末尾的多余空格，确保路径正确
    Set xlWB = xlApp.Workbooks.Open("X:\path_here\" & Format(Date, "mm-DD-yy") & ".xlsx")
    ' 明确通过工作簿对象引用目标工作表
    Set xlSh = xlWB.Sheets("Summary")
    
    xlApp.Visible = True
    
    ' 直接通过工作表对象操作，避免依赖活动对象
    Set xlChart = xlSh.Shapes.AddChart2(297, xlColumnStacked)
    ' 使用指定区域A1:D12作为数据源，而非整列
    xlChart.Chart.SetSourceData Source:=xlSh.Range("A1:D12")
    
    xlWB.Save
    ' 可选：如果不需要保留Excel实例，可以关闭并释放对象
    ' xlWB.Close SaveChanges:=True
    ' xlApp.Quit
    ' Set xlChart = Nothing
    ' Set xlSh = Nothing
    ' Set xlWB = Nothing
    ' Set xlApp = Nothing
End Sub

关键修改说明 #

• 修正路径格式：移除路径末尾的多余空格，确保文件路径拼接正确，能正常打开目标Excel文件。
• 避免活动对象依赖：全程使用预先定义的xlWB、xlSh对象引用工作簿和工作表，摒弃ActiveWorkbook、ActiveSheet、Select这类不稳定的操作。
• 明确数据源范围：将数据源从整列$A:$D改为需求指定的A1:D12，贴合实际业务场景。
• 对象资源清理（可选）：添加对象释放代码，避免Excel进程在后台残留占用资源。

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