Error when running access module

[Authomas]

I made a program to export a an excel file, edit it, and then attach it to an outlook email. Everything works half the time but the other half, i get an error "Run-time error '1004': Methos 'Columns'of object'_Global'failed", clicking debug brings me to my code "Columns("B:B").Delete Shift:=xlToLeft", any ideas as to why this works sometimes and why other times it doesn't, and is their a way to fix it?



Code:

Public Function excelExportNewParts()
    DoCmd.OutputTo acOutputQuery, "qNewPartTemplate", acFormatXLSX, "G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx", False
        
    Dim xlapp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xldata As Excel.Range
    Dim ExcelPath As String
    Dim rowNo As Long




    ExcelPath = CurrentProject.Path & ""
    Set xlapp = CreateObject("Excel.Application")
    Set xlBook = xlapp.Workbooks.Open("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx")
    Set xlSheet = xlBook.Worksheets("qNewPartTemplate")




    xlapp.Visible = True
    xlSheet.Select
    xlSheet.Activate
 
    'With ActiveSheet
        Columns("B:B").Delete Shift:=xlToLeft
        Range("B1:B1") = "Description"
        Cells.Select
        Selection.ClearFormats
        Columns("F:F").NumberFormat = "0.00"
    'End With
    
    xlBook.Save
    xlBook.Close
    
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlapp = Nothing
    
    ' Outlook email
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookAttach As Outlook.Attachment
    
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        .To = "email"
        .Body = "Hello," & vbNewLine & vbNewLine & "Could you please enter these when you get a chance?" & vbNewLine & vbNewLine & "-Thank you!"
        ' Set the Subject of the message.
        If Not IsMissing("New Parts") Then
            .Subject = CStr("New Parts")
        End If
        ' Add attachments to the message.
        If Not IsMissing("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx") Then
            Set objOutlookAttach = .Attachments.Add("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx")
        End If
   End With
   
   objOutlookMsg.Display


   
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing


End Function

[June7]

Try explicitly qualifying object referencing. Use the xlSheet object.

xlSheet.Range("B1:B1")

xlSheet.Columns("B:B")

xlSheet.Cells.Select

Can wrap in a With block:

With xlSheet
     .Columns("B:B").Delete Shift:=xlToLeft
     .Range("B1:B1") = "Description"
     .Cells.ClearFormats
     .Columns("F:F").NumberFormat = "0.00"
End With

Note for future: Please post code between CODE tags to retain indentation and readability.

[Authomas]

Awesome it worked! Thank you! Also sorry about that I updated it and will keep that in mind in the future.