Microsoft Access 2013 Using VBA to create formatted Excel Spreadsheet

I don't have any code handy that will help you but you can figure out some stuff for yourself by using the macro recorder in Excel.   That's how I figured it out originally.

Open a spreadsheet and put in some data that looks like what you will be adding.  Then turn on the macro recorder and go through the motions of formatting a cell to have the attributes you want.  Stop the recorder and examine the code that was generated.  You can copy that code and with a minor tweak to how the workbook is referenced, run it from Access.  If you still need help, I'll post a sample later this evening.

We will also need to know if you are poking values into individual cells or if you are appending many rows and then need to loop through them looking for situations that will control the formatting.

Sometimes, if you are comfortable doing this with Excel, you can create an Excel macro to do all the formatting.  Then you append the data from Access and run the Excel macro to do the formatting.

Thanks. I will try it.

Unfortunately I know access a lot but not Excel. This will be a new database everyday not sure how to get a new excel to create a new macro. Can the macro be in another spreadsheet? But in the mean time I am going to try something with your original idea.

My initial macro run didn't do anything but I found this and it does work but I need the conditional formatting

Public Function Send2Excel(strTableName As String, Optional strSheetName As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to

    
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
    Set rst = CurrentDb.OpenRecordset(strTableName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
        
    Set xlWSh = xlWBk.Worksheets("Sheet1")
    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If
    xlWSh.Range("A1").Select

    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial Narrow"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    [b]    '.BackColor = vbBlue [/b][i]This didn't work[/i]
        End With
        
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
        
        
        
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
   
        
    End With
    ' selects all of the cells
    ApXL.activesheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.activesheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
    rst.Close
    Set rst = Nothing
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
End Function

Select allOpen in new window

not sure what how to tell this to make the background color different based on the status field. Also I don't see where I can put the resulting spreadsheet in the propery directory.

If I have to settle for send2excel without conditional formatting, how do I get it to save this spreadsheet? It just opens it now and leaves it open. Thanks.

Can you use a template in the output that is formatted or an AutoExec macro to format the file after output?

I use

e DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryofrecordsexcel", "C:Database Request Log.xls", True

Select allOpen in new window

I open the database at 6:00 AM so it is automated.

I use

e DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryofrecordsexcel", "C:Database Request Log.xls", True
                                           
Select allOpen in new window

I open the database at 6:00 AM so it is automated.

I don't understand, how does that relate to any of the above suggestions?

This was a very difficult problem but the solution used details from the two answers. Thank you for the assistance.