VBA Access中Method 'columns' of object '_Global'失败错误求助

已成功从Access导出查询到Excel文件，但调用格式化函数时，首次运行正常，第二次为其他收件人处理时，在Columns("A:A").Select行触发Method “columns” of object “_Global” failed error错误。

原格式化函数代码： #

Private Sub formatExcelOuputQty()
    BasePath = "W:\040 MAGAZZINO\INVENTARIO "
    strPathAttach = BasePath & Year(Now()) & "\" & CustSupp.Value & "\Richiesta inventario fornitore " & CustSupp.Value & " - anno " & Year(Now()) & ".xls"
    
    Dim appExcel As Variant
    Dim MyStr As String
    Dim rng As Excel.Range
    Dim wksNew As Worksheet
    
    ' Open Excel file
    'Set appExcel = CreateObject("Excel.application")
    Set appExcel = New Excel.Application
    appExcel.Visible = False
    appExcel.DisplayAlerts = False
    appExcel.Workbooks.Open (strPathAttach)
    Set wksNew = appExcel.Worksheets("QRY_GiacenzeFornitoriNoStorage")
    wksNew.Name = "Giacenze"
    'appExcel.Visible = True
    
    With wksNew
        Columns("A:A").Select
        Selection.Font.Bold = False
        Selection.Font.Bold = True
        Rows("1:1").Select
        Selection.Font.Bold = False
        Selection.Font.Bold = True
        Range("A1:G1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Cells.Select
        Cells.EntireColumn.AutoFit
        Selection.RowHeight = 15
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        With Selection.Font
            .Name = "Calibri"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        With Selection.Font
            .Name = "Calibri"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        Range("B2").Select
        ActiveWindow.FreezePanes = True
        Range("G2").Select
        
        appExcel.ActiveWorkbook.SaveAs FileName:= _
          BasePath & Year(Now()) & "\" & CustSupp.Value & "\Richiesta inventario fornitore " & CustSupp.Value & " - anno " & Year(Now()) & ".xlsx" _
          , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        '.ActiveWorkbook.Save
        appExcel.ActiveWorkbook.Close
        appExcel.Visible = False
    End With
    
    Set wksNew = Nothing
    Set wb = Nothing
    
    appExcel.Quit
    
    Set appExcel = Nothing
    
    If Not (appExcel Is Nothing) Then
        appExcel.Close (False)
        Set appExcel = Nothing
    End If
End Sub

问题原因与修复方案 #

核心问题 #

在With wksNew代码块中，Columns、Rows、Range等对象未添加.前缀，导致默认指向Access的全局对象（而非目标Excel工作表）。首次运行时Excel实例残留可能侥幸生效，第二次运行时上下文混乱触发报错。

修复步骤 #

1. 明确绑定Excel对象：在With wksNew块内，所有Columns、Rows、Range、Cells前添加.，确保指向当前工作表。
2. 移除冗余的Select/Selection：直接操作单元格对象，避免依赖选中状态引发的上下文错误。
3. 修正资源清理逻辑：删除未定义的wb变量，调整Excel实例退出顺序，避免无效的二次关闭操作。

修复后的完整代码 #

Private Sub formatExcelOuputQty()
    Dim BasePath As String, strPathAttach As String
    BasePath = "W:\040 MAGAZZINO\INVENTARIO "
    strPathAttach = BasePath & Year(Now()) & "\" & CustSupp.Value & "\Richiesta inventario fornitore " & CustSupp.Value & " - anno " & Year(Now()) & ".xls"
    
    Dim appExcel As Excel.Application
    Dim wksNew As Excel.Worksheet
    
    ' 初始化Excel实例
    Set appExcel = New Excel.Application
    appExcel.Visible = False
    appExcel.DisplayAlerts = False
    
    ' 打开工作簿并指定工作表
    appExcel.Workbooks.Open strPathAttach
    Set wksNew = appExcel.Worksheets("QRY_GiacenzeFornitoriNoStorage")
    wksNew.Name = "Giacenze"
    
    With wksNew
        ' 直接设置字体加粗，无需Select
        .Columns("A:A").Font.Bold = True
        .Rows("1:1").Font.Bold = True
        
        ' 设置表头背景样式
        With .Range("A1:G1").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        
        ' 批量设置单元格格式
        With .Cells
            .EntireColumn.AutoFit
            .RowHeight = 15
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            
            With .Font
                .Name = "Calibri"
                .Size = 11
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .ThemeFont = xlThemeFontNone
            End With
        End With
        
        ' 设置冻结窗格
        .Range("B2").Select
        appExcel.ActiveWindow.FreezePanes = True
    End With
    
    ' 保存为xlsx格式并关闭工作簿
    appExcel.ActiveWorkbook.SaveAs _
        Filename:=BasePath & Year(Now()) & "\" & CustSupp.Value & "\Richiesta inventario fornitore " & CustSupp.Value & " - anno " & Year(Now()) & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    appExcel.ActiveWorkbook.Close
    
    ' 清理资源
    Set wksNew = Nothing
    appExcel.Quit
    Set appExcel = Nothing
End Sub

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