Excel automation is difficult at best. It takes a lot of testing to get it to work.... but it is possible.
Try this... it is a mix of my code and yours:
Code:
Option Compare Database
Option Explicit
Sub TestExcelFormatting()
Dim XLapp As Object
Dim xlWB As Object
Dim xlSht As Object
Dim Sht As Object
Dim blnEXCEL As Boolean
Dim xlCol As Long
Dim xlRow As Long
Dim i As Integer
Dim dbs As DAO.Database
Dim rstUnit As DAO.Recordset
Dim NewFileName As String 'full Excel file name, including dir path, without extension
Dim xlName As String 'Full Excel name, including extension
Dim xlObj As Object
Dim strTemp As String 'ends up as the query name
Dim strPath As String 'directory path for Excel file
Dim strDate As String 'formated date for the Excel file name
Dim strRST_SQL As String 'SQL statement to set up the recordset
Dim strEXPORT_SQL As String 'SQL for the query
Dim sheetName As String 'sheetname will equal the unit title (ex. "Unit 2" or "ADMIN")
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set XLapp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set XLapp = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
'**** Set this to false if you DO NOT want to see the workbook/worksheets
XLapp.Visible = True
'****
NewFileName = "C:\Users\XXXXX\Documents\DayCamp 2018\Copy of 2018DayCampRoster04-07" 'this is a test workbook. When this code is working, it will be at the end of the sub that creates this file.
xlName = NewFileName & ".xlsx" 'this is needed b/c the NewFileName is created in an earlier portion of the sub
' Debug.Print xlName 'so I know the code has started
Set xlWB = XLapp.Workbooks.Open(xlName)
' Debug.Print XLapp.activeworkbook.Worksheets.Count
For Each xlSht In XLapp.activeworkbook.Worksheets
' Debug.Print xlSht.Name
xlSht.Select
xlSht.Cells.EntireColumn.AutoFit
xlSht.Range("1:1").Font.Bold = True
xlSht.Range("A1:P1").Select
With xlSht.Selection.Interior
.Pattern = 1 'constant for xlSolid
.PatternColorIndex = -4105 'constant for xlAutomatic
.ThemeColor = 1 'constant for xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
xlSht.Range("A1").Select
Next xlSht
'select the first worksheet
XLapp.activeworkbook.Worksheets(1).Select
xlWB.Close SaveChanges:=True
DoEvents '<<<-- I found this is needed to give windows time to save the file....
Set xlWB = Nothing
If blnEXCEL = True Then
XLapp.Quit
End If
Set XLapp = Nothing
MsgBox "Done"
End Sub