Method 'columns' of object '_Global' failed error - VBA Access

Question

Good morning everyone. I am trying to format an Excel file generated by exporting an Access query, I can correctly export the query and save the Excel file in the relevant folder, but when I call the function to format the file I get the error Method “columns” of object “_Global” failed error at the line ‘Columns(’A:A").Select’. Below is the formatting function:

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

Can you help me? Thank you.

Important information: the function works the first time I use it, the error appears the second time, when I try to format the file for another addressee.

Answer 1

It's very time consuming to open and close a workbook while debugging formatting. Here I extracted the formatting responsibilities to it's own method.

We should always carefully read recorded Macros and remove duplicated code, and object selection whenever possible.

Option Explicit

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 Excel.Application
    Dim wb As Workbook, ws As Worksheet
    
    ' Open Excel file
    Set appExcel = New Excel.Application
    appExcel.ScreenUpdating = False
    appExcel.Visible = False
    appExcel.DisplayAlerts = False
    Set wb = appExcel.Workbooks.Open(strPathAttach)
    Set ws = wb.Worksheets("QRY_GiacenzeFornitoriNoStorage")
        
    ApplyExcelOuputQtyFormatting ws
    wb.SaveAs Filename:= _
        BasePath & Year(Now()) & "\" & CustSupp.Value & "\Richiesta inventario fornitore " & CustSupp.Value & " - anno " & Year(Now()) & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            
    wb.Close SaveChanges:=False
    appExcel.Quit
    appExcel.ScreenUpdating = True
End Sub
    
Private Sub ApplyExcelOuputQtyFormatting(ws As Worksheet)
    With ws
        .Name = "Giacenze"
        .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
        .Application.Goto .Range("B2")
        ActiveWindow.FreezePanes = True
    End With
    
End Sub