Exporting Help

[manic]

I found this code maybe on this site or another one, on how to export a MS Access table or Query to and Excel file and it works great.
My lack of knowledge would not let me figure out how to make this code Export multiple table into one Excel file any help with this will be appreciated it.
here is the Function:

Sub ExportData_Sheet_Basic()
On Error GoTo ExportData_Error

'DAO objects to get the data
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

'Excel objects to manipulate Excel
    Dim exApp As Excel.Application
    Dim exBook As Excel.Workbook
    Dim exSheet As Excel.Worksheet

'variables to use for formatting loops
    Dim NoOfCols As Integer
    Dim NoOfRows As Integer

'Iterant for misc loops
    Dim i As Integer

'Workbook name
Dim BookName As String

'Instantiate the database object
    Set db = Application.CurrentDb

'Get the data
    Set rs = db.OpenRecordset("SELECT* FROM tbl_Coating_RBS_Data ")
    
'Instantiate the excel objects
    Set exApp = New Excel.Application

'Get the workbook name
    BookName = Mid(db.Name, 1, InStrRev(db.Name, "\")) & "MyTemplate.xls"
    
'Check workbook template exists
    If Dir(BookName) = vbNullString Then
'It doesn't... so create it
        
        Set exBook = exApp.Workbooks.Add
        
    Else
'It does... so open it
        Set exBook = exApp.Workbooks.Open(BookName)
    
    End If

'Change the filename so you don't overwrite your template
    BookName = Replace(Replace(BookName, ".xls", "") & "_" & Year(Date) & "_" & Month(Date) & "_" & Day(Date) & "__" & Replace(Replace(Format(Time(), "medium time"), " ", "_"), ":", "-"), "MyTemplate", "Basic_Export") & "_a"
 
'We don't want to save over any other reports so we tack a character to the end of the name.
'Note we make sure the loop runs once at a minimum.
    Do
        i = i + 1
        
        BookName = Mid(BookName, 1, Len(BookName) - 1) & Chr(96 + i)
        
    Loop While Dir(BookName & ".xls") <> vbNullString

'Add the extension
    BookName = BookName & ".xls"

'Save it
    exBook.SaveAs BookName
    
    exApp.Visible = True
    
    exApp.Interactive = False

'Instantiate the exSheet object to the specific work sheet you want to play with
    Set exSheet = exBook.Worksheets(1)
    
    exSheet.Activate

'Populate recordset to get accurate record count
    If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
    
'Populate the variables - note we don't deduct one from the values as
'Excel arrays and cells start at 1 not 0
    NoOfCols = rs.Fields.Count
    NoOfRows = rs.RecordCount

'Pop the data into Excel
    exSheet.Range("A2").CopyFromRecordset rs

'Write in the column headings
    For i = 0 To NoOfCols - 1
        
        exSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    
    Next i

'Use our variables to format the data populated cells ONLY
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Interior.Color = vbBlue

'Use our variables to format the data populated cells ONLY
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Font.Color = vbWhite

'And again - using both this time
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & (NoOfRows + 1)).Borders.Color = RGB(0, 0, 0)

'Adjust column widths
    exSheet.Columns.EntireColumn.AutoFit

'Save it
    exBook.Save

ExportData_Exit:
'We don't want an error here otherwise we loop forever
On Error Resume Next

'Very important - always account for in error trap
    exApp.Interactive = True

'Clean Up
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    Set exSheet = Nothing
    Set exBook = Nothing
    Set exApp = Nothing
    
    Exit Sub
    
ExportData_Error:
    
    MsgBox Err.Description
    Resume ExportData_Exit
End Sub

[help_me_with_access]

it looks like they're exporting a table to xl one cell or row at a time. why don't you just write a small loop in access and use the function "transferspreadsheet" to transfer all of your tables to the same workbook?

[manic]

it looks like they're exporting a table to xl one cell or row at a time. why don't you just write a small loop in access and use the function "transferspreadsheet" to transfer all of your tables to the same workbook?

I can do that but how can I format it at the same time?

[help_me_with_access]

I can do that but how can I format it at the same time?

you can't. why do you need formatting?

any pattern to the formatting? if there is, just create named ranges or other resource that are easily referenced for formatting purposes. that would be an option I would think, right?

[manic]

you can't. why do you need formatting?

any pattern to the formatting? if there is, just create named ranges or other resource that are easily referenced for formatting purposes. that would be an option I would think, right?

Is there a way that by doing this export you can assign a header and color to the output worksheet?

[help_me_with_access]

of course you can. you can do it through either program. but you obviously have to do it through an instance of excel, regardless of where the coding is executing from.