Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: Exporting multiple tables into separate Excel tabs

    Is there a way to automate the exporting of multiple Access tables into separate Excel worksheets?

    or do I have to export it all in one table then use a macro to separate it out?

    any ideas would be most grateful

  2. #2
    Join Date
    Apr 2004
    Kingsland, Georgia
    If you add the excel object library to your references in the code window, you can automate excel. So you could (via access vba) create a new excel file, add a worksheet, dump records from the table onto said worksheet, create another worksheet, dump more records, etc.

  3. #3
    Join Date
    Apr 2004
    Kingsland, Georgia
    if you're new to excel automation, you may want to check out the post below yours on the main page (at least it's below yours right now). he's given an example for his problem that you could use to get started...

  4. #4
    Join Date
    Jul 2008
    Here is a real basic framework you can use. I didn't include the code to move the data from your source to the sheet because there are several ways of doing it and I'm not sure what would work best for your application. What is included is how to make a new sheet, name it and move it into position.

    Option Compare Database 'Use database order for string comparisons
        Dim MyExcel As Object 'This is the excel object
        Dim MyBook As Object
        Dim MySheet As Object
    'This uses the DAO method of opening a query data source
    Sub My_Excel_Book()
    On Error GoTo My_Excel_Err  'Comment this line out if you need to debug in VB editor
        Dim Database As DAO.Database
        Dim MyRecSet As DAO.Recordset
        Dim Filename As String
        Dim BarCnt As Long, BarVal As Variant    'Variables for the progess bar in the task bar area
        'Set up the database source data. This example is query source
        Set Database = CurrentDb
        Set MyRecSet = Database.OpenRecordset("Acad_Union_Query", dbOpenDynaset)
        'Set up the Excel objects
        Set MyExcel = CreateObject("Excel.Application")
        MyExcel.Visible = False
        Set MyBook = MyExcel.Workbooks.Add
        Set MySheet = MyBook.Worksheets.Add			'Add first new worksheet to book
        MySheet.Move After:=MyBook.Sheets(MyBook.Sheets.Count)	'Move worksheet to last sheet position
        MsgBox "This will Copy " & MyRecSet.RecordCount & " records" & vbCr & "into an Excel file in C:\temp\..."
        'Set the first sheet name
        MySheet.Name = MyRecSet![My_Field_Name] 'Set the name of the first sheet
        'Set up and initialize the optional progress bar
        BarCnt = MyRecSet.RecordCount
        BarVal = SysCmd(acSysCmdInitMeter, "Building New Excel Sheet...", BarCnt)
        BarCnt = 1
        Do While Not MyRecSet.EOF
            'Your code goes here that moves data from the access source to the excel sheet
            'Now we start a new sheet, name and move it into position
            Set MySheet = MyBook.Worksheets.Add
            MySheet.Move After:=MyBook.Sheets(MyBook.Sheets.Count)
            MySheet.Name = MyRecSet![My_Field_Name] 'Set the name of the new sheet
            'This is optional if you want a progress bar...	
            BarVal = SysCmd(acSysCmdUpdateMeter, BarCnt) 'Update the optional progress bar
            BarCnt = BarCnt + 1   'Increment the optional progress Bar Count
        'Save the Excel file and do some clean-up prior to exit
        Filename = "C:\temp\My_Excel " & Format(Now(), "mm-dd-yy hh-mm-ss") & ".xls"
        MyBook.SaveAs (Filename)
        Set MySheet = Nothing
        Set MyBook = Nothing
        Set MyExcel = Nothing
        Set MyRecSet = Nothing
        MsgBox "File saved to:" & vbCr & vbCr & Filename    'vbCr is a visual basic carriage return
        BarVal = SysCmd(acSysCmdRemoveMeter) 'Remove the optional progress meter
        Exit Sub
        MsgBox Error$
        Resume My_Excel_Cleanup
    End Sub
    At the end of the code, the excel file is saved to a folder called C:\temp\. So make sure this temp folder exists on you machine befor running the code. Hope this helps! Bill
    Last edited by Bill322; 08-05-08 at 13:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts