Access to Excel export with proper formatting

The below code exports information for me from Access to Excel, and this works perfectly, however, I need for the export to properly format my excel sheets before the export (bolding, column sizes, subtotals,etc.). Based on the coding below, what would I need to add to this in order for this to work?

Private Sub Command4_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT field1 FROM table ORDER By field1;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.* FROM table WHERE table.field1 = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\file " & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "" & strCrt
rs.MoveNext
Loop
End Sub

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

You can easily format the Excel file from Access. Check this thread out on how I do it.

hiding excel manipulations and showing a progress bar

I'm a novice at VBA, but what the code above does is spit out about 60 or so different excel files.

I've copied the code on the other thread, let me know if I put the CALL code in the correct place, also, is there any code that i need to modify myself other than the file name before the .xls?

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats
Call ModifyExportedExcelFileFormats("X:\file.xls")

Application.SetOption "Show Status Bar", True

vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting exported files... please wait.")

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

With xlApp
.Application.Sheets("YourSheetName").Select
.Application.Cells.Select
.Application.Selection.ClearFormats
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True
.Application.Cells.Select
.Application.Selection.RowHeight = 12.75
.Application.Selection.Columns.AutoFit
.Application.Range("A2").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Range("A1").Select
.Application.Selection.AutoFilter
End If
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit
End With

Set xlApp = Nothing
Set xlSheet = Nothing

vStatusBar = SysCmd(acSysCmdClearStatus)

Exit_ModifyExportedExcelFileFormats:
Exit Sub

Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats
End Sub

I’m very new to VBA but would like to use this VBA code to export my access tables to excel with proper formatting

Could someone please describe how I would go about importing this code into my database?

Thanks for taking the time to help a new be understand