VBA中wb.SaveAs()与wb.Close()意外关闭所有工作簿的问题排查

问题现象 #

遍历多个Excel工作簿添加图表，执行wb.SaveAs时会关闭所有打开的工作簿（包括运行脚本的宏启用工作簿），且无任何报错提示。

原代码 #

Sub addChartsAndFormatting()

Dim wb As Workbook
Dim wbMacro As Workbook
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim lr As Long
Dim lc As Long
Dim fso As FileSystemObject
Dim ch As Chart
Dim dt As Range

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
oFolderPath = "C:\Users\rs\3_EditedWithAnalyses"
saveFilePath = "C:\Users\rs\4_EditedWithCharts"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbMacro = ThisWorkbook

For Each oFile In fso.GetFolder(oFolderPath).Files

    
    Set wb = Workbooks.Open(oFile.Path)
    Set ws = wb.Sheets("Sheet1")
    Set dt = Range("BG18:BJ18")
    Set ch = ws.Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=ws.Range("BE22")).Chart


    With ch
        .SetSourceData Source:=dt
        .ChartTitle.Text = "Seasonal Emissions Rate"
        .FullSeriesCollection(1).Name = "Sheet1!$BG$2:$BJ$2"
        .FullSeriesCollection(1).Values = "Sheet1!$BG$18:$BJ$18"
        .FullSeriesCollection(1).XValues = "Sheet1!$BG$2:$BJ$2"
        .HasLegend = False
        With .Axes(xlValue)
            .HasTitle = True
            With .AxisTitle
                .Caption = "Average Emissions Rate (lb CO2/MWh-gross)"
            End With
        End With
    End With
    
    
    rawFile = Split(oFile.Name, ".")
    saveFileName = rawFile(0) & "_complete.xlsx"
    wb.SaveAs (saveFileName) ' <------ 问题触发点
    wb.Close
    
Next oFile

End Sub

问题根源及修复方案 #

1. 未指定完整保存路径 #

原代码中saveFileName仅包含文件名，未结合定义好的saveFilePath，导致文件会保存到Excel的默认路径（而非你指定的4_EditedWithCharts文件夹）。如果默认路径下存在同名文件、权限不足或路径不存在，会触发未捕获的隐性异常，导致所有工作簿被关闭。

修复：用FileSystemObject的BuildPath方法拼接完整路径：

saveFileName = fso.BuildPath(saveFilePath, rawFile(0) & "_complete.xlsx")

2. Range对象未限定父工作表 #

Set dt = Range("BG18:BJ18")未指定属于当前操作的ws工作表，VBA会默认使用活动工作表，如果活动工作表不是目标工作表，会导致图表数据源引用错误，进而影响后续保存操作的稳定性。

修复：明确指定Range的父对象为ws：

Set dt = ws.Range("BG18:BJ18")

3. SaveAs方法调用不规范 #

wb.SaveAs (saveFileName)中的括号会强制将saveFileName作为表达式求值，可能导致参数传递异常。同时未明确指定文件格式，当原文件是宏启用格式时，保存为普通xlsx可能触发格式转换的隐性错误。

修复：去掉多余括号，明确指定文件格式：

wb.SaveAs Filename:=saveFileName, FileFormat:=xlOpenXMLWorkbook

修改后的完整代码 #

Sub addChartsAndFormatting()

Dim wb As Workbook
Dim wbMacro As Workbook
Dim ws As Worksheet
Dim fso As FileSystemObject
Dim ch As Chart
Dim dt As Range
Dim oFolderPath As String
Dim saveFilePath As String
Dim rawFile As Variant
Dim saveFileName As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
oFolderPath = "C:\Users\rs\3_EditedWithAnalyses"
saveFilePath = "C:\Users\rs\4_EditedWithCharts"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbMacro = ThisWorkbook

' 确保保存文件夹存在，避免路径不存在报错
If Not fso.FolderExists(saveFilePath) Then
    fso.CreateFolder saveFilePath
End If

For Each oFile In fso.GetFolder(oFolderPath).Files
    ' 只处理Excel文件，避免遍历到其他类型文件
    If LCase(fso.GetExtensionName(oFile.Path)) = "xlsx" Or _
       LCase(fso.GetExtensionName(oFile.Path)) = "xlsm" Then
        
        Set wb = Workbooks.Open(oFile.Path)
        Set ws = wb.Sheets("Sheet1")
        Set dt = ws.Range("BG18:BJ18")
        Set ch = ws.Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=ws.Range("BE22").Left).Chart

        With ch
            .SetSourceData Source:=dt
            .ChartTitle.Text = "Seasonal Emissions Rate"
            .FullSeriesCollection(1).Name = ws.Range("BG2:BJ2").Value
            .FullSeriesCollection(1).Values = ws.Range("BG18:BJ18")
            .FullSeriesCollection(1).XValues = ws.Range("BG2:BJ2")
            .HasLegend = False
            With .Axes(xlValue)
                .HasTitle = True
                .AxisTitle.Caption = "Average Emissions Rate (lb CO2/MWh-gross)"
            End With
        End With
        
        rawFile = Split(oFile.Name, ".")
        saveFileName = fso.BuildPath(saveFilePath, rawFile(0) & "_complete.xlsx")
        wb.SaveAs Filename:=saveFileName, FileFormat:=xlOpenXMLWorkbook
        wb.Close SaveChanges:=False
    End If
Next oFile

End Sub

额外优化：添加了保存文件夹存在性检查，以及只处理Excel文件的判断，避免无效文件导致的异常。

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