Option Compare Database
Option Explicit
 
Sub ExportReport24Changes()
     '   workbook's name
    Const szWorkbook As String = "\Report 24 2.xls"
        
     '   Access query where the info comes from
    Const szQueryName As String = "report 24 changes"
     
     '   Workbook is created in this path
    Dim szFullPath As String
    szFullPath = "S:\HR\Performance Team\HR Programme Office\MI\1. Weekly Reports\Report 24\Temporary Files" & szWorkbook
     
    On Error GoTo ErrHandle
    With Application
        .Echo False
         
         '       Output the query to our Excel book
        DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullPath, False
         
         '       Late binding to avoid reference:
        Dim xlApp As Object 'Excel.Application
        Dim xlwb As Object 'Workbook
        Dim xlws As Object 'Worksheet
         
         '       Create the instance of Excel that we will use to open the workbook
        Set xlApp = CreateObject("Excel.Application")
        Set xlwb = xlApp.Workbooks.Open(szFullPath)
        Set xlws = xlwb.Worksheets(szQueryName)
         

         '       Format our report
        xlws.Range("A1:AH1").Font.Bold = True
        xlws.Range("A1:AH1").Font.ColorIndex = 1
        xlws.Range("A1:AH1").Interior.ColorIndex = 2

        '       AutoFit the columns
        xlws.Range("A:AH").Columns.AutoFit

        
         '       Select to make sure the sheet has focus
        xlws.Range("A1").Select
         
         '       Close the file we created
        xlwb.Close True
         
ErrorExit:
         '       Explicitly Clear Memory
        Set xlws = Nothing
        Set xlwb = Nothing
        Set xlApp = Nothing
         
        .Echo True
    End With
    Exit Sub
     
ErrHandle:
    MsgBox Err.Description
    GoTo ErrorExit
    
End Sub