Export Access Query To Multiple Excel Worsheets in 1 Workbook

[jawilli1]

Hi Folks,
I want to export a query that I have to an excel document. That is the easy part though. My client also wants it exported to where each particular buildings schedule is on it's own worksheet within the 1 workbook. Example Of what I'm working with is below

  Select tblCalendar.date, tblCalendar.Event, tblCalendar.time, tblCalendar.Building from tblCalendar Where tblCalendar.date between #12/12/2005# And #12/25/2005#

There can be any number of buildings that are scheduled for, so the number of worksheets (tabs) in the workbook needs to be variable and dicated by the result set.

Essentially he wants each buildings schedule (tblCalendar.Building) to be on it's own worksheet within the one workbook. Does anyone know how to do this? My search has been unsuccessful as of yet. Any input is GREATLY appreciated!

 

[nateobot]

Just last month I did something eerily similar to this.

It was in VB. We had a list of Cost Centers and within each Cost Center there is a list of Managers. For reporting we needed to create a seperate workbook for each Cost Center and then a worksheet for each manager in the Cost Center.

How I went about it was to get a recordset of all the Managers. For each manager I would run the following code to build a WorkBook for them:

Option Explicit

Private xlApp As Excel.Application

Public Sub BuildBook(CostCenter As String)
    
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim i As Integer
    
    Set xlApp = CreateObject("excel.application")
    xlApp.DisplayAlerts = False
    Set xlBook = xlApp.Workbooks.Add
    DoEvents
    xlApp.Visible = False
'function to see if the file already exists    
    If FileExists(SAVE_PATH & CostCenter & ".xls") Then

        FileCopy SAVE_PATH & CostCenter & ".xls", SAVE_PATH & CostCenter & ".001"
    End If
    xlBook.SaveAs (SAVE_PATH & CostCenter & ".xls")
    'turn off gridlines
    
    'Lets start with a clean worksheet.
    'start with 2 since we need to keep at least one worksheet in the book.
    For i = xlBook.Worksheets.count To 2 Step -1
        xlBook.Worksheets(i).Delete
    Next
    'Create main sheet
    Set xlSheet = xlBook.Worksheets(1)
    xlApp.ActiveWindow.DisplayGridlines = False
    'loop through the CCCodes for this user and create all the worksheets.
    'call the user loop to the db and build the type

'SetData queries the database for all the managers in this CostCenter.  It then sets values to a User Defined Type called "xlStore".
'After it sets those values it then calls the BuildSheet sub.
    Call SetData(CostCenter)
    'cleanup
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Activate
    xlBook.Close True, SAVE_PATH & CostCenter & ".xls"
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

I only included the pertinant section for your needs. This Sub is very large because it uses the UDT to place data in certain cells.

Public Sub BuildSheet()
    Dim xlSheet As Excel.Worksheet
    
    'Query the database and bring back the items needed for the sheet.
        
    Set xlSheet = xlApp.ActiveWorkbook.Worksheets.Add
    With xlSheet
        .Select
        '.Move After:=Sheets(xlApp.ActiveWorkbook.Worksheets.Count)
        .Name = xlStore.sReconCode
'...
'yada yada yada

As stated before this was coded in VB, but should give you at least a starting point and some code to use.

 

[jawilli1]

I don't want to sound like an idiot, but i'm not too able to follow that code, where are you assigning the information to xlstore? Maybe I need an sample app with minimal capabilities to better guide me on my quest.

 

[voskouee]

How would i change your this code to get different worksheets for an array of tables in my database. each sheet should have the name of the table and the file of the spreasheet should be something + a date? any suggestions?

Just last month I did something eerily similar to this.

It was in VB. We had a list of Cost Centers and within each Cost Center there is a list of Managers. For reporting we needed to create a seperate workbook for each Cost Center and then a worksheet for each manager in the Cost Center.

How I went about it was to get a recordset of all the Managers. For each manager I would run the following code to build a WorkBook for them:

Option Explicit

Private xlApp As Excel.Application

Public Sub BuildBook(CostCenter As String)
    
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim i As Integer
    
    Set xlApp = CreateObject("excel.application")
    xlApp.DisplayAlerts = False
    Set xlBook = xlApp.Workbooks.Add
    DoEvents
    xlApp.Visible = False
'function to see if the file already exists    
    If FileExists(SAVE_PATH & CostCenter & ".xls") Then

        FileCopy SAVE_PATH & CostCenter & ".xls", SAVE_PATH & CostCenter & ".001"
    End If
    xlBook.SaveAs (SAVE_PATH & CostCenter & ".xls")
    'turn off gridlines
    
    'Lets start with a clean worksheet.
    'start with 2 since we need to keep at least one worksheet in the book.
    For i = xlBook.Worksheets.count To 2 Step -1
        xlBook.Worksheets(i).Delete
    Next
    'Create main sheet
    Set xlSheet = xlBook.Worksheets(1)
    xlApp.ActiveWindow.DisplayGridlines = False
    'loop through the CCCodes for this user and create all the worksheets.
    'call the user loop to the db and build the type

'SetData queries the database for all the managers in this CostCenter.  It then sets values to a User Defined Type called "xlStore".
'After it sets those values it then calls the BuildSheet sub.
    Call SetData(CostCenter)
    'cleanup
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Activate
    xlBook.Close True, SAVE_PATH & CostCenter & ".xls"
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

I only included the pertinant section for your needs. This Sub is very large because it uses the UDT to place data in certain cells.

Public Sub BuildSheet()
    Dim xlSheet As Excel.Worksheet
    
    'Query the database and bring back the items needed for the sheet.
        
    Set xlSheet = xlApp.ActiveWorkbook.Worksheets.Add
    With xlSheet
        .Select
        '.Move After:=Sheets(xlApp.ActiveWorkbook.Worksheets.Count)
        .Name = xlStore.sReconCode
'...
'yada yada yada

As stated before this was coded in VB, but should give you at least a starting point and some code to use.