I am using the Docmd.OutputTo (also tried DoCmd.TransferSpreadsheet) to export MS Access query results to a MS Excel spreadsheet. I have a series of queries that I would like to export to separate worksheets within a single workbook, rather that export each to a separate workbook.

Is there a way to tweak the command so it will do what I want?

Appreciate any help that anyone can offer.

Jim Nicholson
jimnich@earthlink.net

We do this all the time:

DO NOT INCLUDE the Curly Braces "{" or "}" in the syntax.

Rand

We haven't tried consolidating the information from several queries into a single spreadsheet, however - this might work:

Rand

The help file in Access 2000 does not provide parameter for choosing a sheet for the DoCmd.Transfer methods. Presumably the method will always write to the first sheet. I myself would always use Excel automation code to write to the Excel sheet in order to have better formatting control but that may be an excessive learning curve if you are happy with the results of the TransferSpreadsheet method. In this case, you could write some automation code that inserted a new sheet for each export.

In order to write the automation code, I would suggest you go to an Excel workbook, turn on the macro recorder, insert a sheet, close the macro recorder and have a peek at the code generated by the macro window. This code will almost drop into your Access code window for reuse. You can open the Excel book without displaying the Excel window on the task bar using the CreateObject call below. There is additional code below to grab an existing instance of Excel, if one exists, with an optional parameter to determine whether the application should close Excel it it is the only consumer of Excel objects so you don't dump the user out of existing Excel workbooks. The code below is just to get you started and will work, as written (early bound), only if you add a reference to Excel in a module window.

Public Function ExcelWorkBook(obj As Excel.Application, strFilePath As String) As Excel.Workbook

    On Error GoTo ErrorHandler

    fnReturnExcel obj
    Set ExcelWorkBook = obj.Workbooks.Open(strFilePath)

ExitRoutine:
    On Error Resume Next
    Exit Function
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - ExcelWorkBook"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function

Public Function fnReturnExcel(obj As Excel.Application, Optional blnOKtoClose As Boolean)

    On Error Resume Next

    Set obj = GetObject(, "Excel.Application")
    If obj Is Nothing Then
        blnOKtoClose = True
        Set obj = CreateObject("Excel.Application")
    End If
End Function

You can call the above procedures with something like:

Dim oExlApp As Excel.Application
Dim oExlWkb As Excel.Workbook

    Set oExlWkb = ExcelWorkBook(oExlApp, "S:\Office\Invoices.xls")
' oExlApp.Visible = True

For one time use you may just write all the above inline rather than as separate procedures. To insert a worksheet, you'll also need to dimension an Excel.Worksheet object. Make sure you close and set to nothing all Excel objects you consume because Excel is finnicky in this regard.

Note that because I don't use TransferSpreadsheet (or any of the transfer methods for that matter) I can't address all the likely issues. It will probably be necessary to close the Excel file each time before you run the transfer after inserting your sheet.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com

Ummm, I hate to rain on everyone's coding parade - but if you export a query to excel, it exports it to a worksheet with the title of that query.
Therefore, if you export 5 queries to the same excel file, it will export them to 5 worksheets in that same file.

No need for Excel Automation or any real coding at all.

It's actually one of the easiest things to do. just do this:
That will export Query1 and Query2 to C:\SomeFile.xls with 2 worksheets, titled Query1 and Query2

It's that easy

I am a loud man with a very large hat. This means I am in charge

Useful to know. I have not had occasion to ever use the TransferSpreadsheet method because when I started exporting to Excel, it was necessary to use automation to do things beyond the scope of the basic transfer methods. I always use File I/O when working with text files in order to trap errors and to control formatting beyond what the defaults provide. I've been doing automation with Word, Excel, Outlook, Powerpoint and Project for years and dabbled with Autocad and Visio automation so I find it a natural way to proceed.

Your solution certainly answers the question more succinctly.

Ciao
Jurgen Welz
Edmonton AB Canada
jwelz@hotmail.com

This is pretty good, but can anyone help me with something slightly more difficult? I need to output a number of queries to the same sheet on excel. Each query contains its own information so they cant be combined into one, but I need to output these to the same sheet within excel (preferably under one another as opossed to side by side).

Can anyone help?

Does that mean each result set has a different schema (different column names)? If so, not sure I'd want to touch that one (yikes!), but if you are working with queries with similar but different schema, you might try something like the following:

The Sub below adds two result sets to a single Excel spreadsheet. The first result set lists all of your customers in a given city, and the second result set lists all of your service reps in that city. The two result sets are separated by a blank line on the spread sheet. The results look like:

ID Name City

BOLID Bólido Comidas preparadas Madrid
FISSA FISSA Fabrica Inter. Salchichas S.A. Madrid
ROMEY Romero y tomillo Madrid

5 "Buchanan, Steven" Madrid
1 "Davolio, Nancy" Madrid
2 "Fuller, Andrew" Madrid
3 "Leverling, Janet" Madrid
4 "Peacock, Margaret" Madrid

Notice that the results sets share a similar schema. If your schema are really different, you might be able to get this to fly using some pretty generic column headers.

The three columns in the template file are: ID, Name, City

The module copies the template file to an output directory before filling it using a FileSystem object, so you’ll need to reference the Microsoft Scripting Runtime library. The code opens an Excel database connection using the Jet driver, then opens two recordset objects which are used to populate the spreadsheet.

The module is called with something like:

ExportToExcel "C:\Output\CompaniesEmployeesList.xls"

~~~~~Code~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub ExportToExcel(strOutputFile As String, Optional boolSuppressMessages As Boolean = False)

    Dim strTemplateFile As String
    Dim fso As Scripting.FileSystemObject
    Dim cnn As ADODB.Connection
    Dim rstTarget As ADODB.Recordset
    Dim rstCustomers As ADODB.Recordset
    Dim rstServiceReps As ADODB.Recordset

    On Error GoTo ErrorHandler: On Error GoTo 0

    Set fso = New Scripting.FileSystemObject

    ' Get template file path
    strTemplateFile = CurrentProject.path & "\CompaniesEmployeesList.xlt"

    ' Copy template to the target
    fso.CopyFile strTemplateFile, strOutputFile, False

    ' Open a connection to the workbook
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.ConnectionString = "Data Source=" & strOutputFile & ";" & _
                       "Extended Properties=""Excel 8.0"""
    cnn.Mode = adModeReadWrite
    cnn.Open

    ' Open the target recordset (the Excel sheet)
    Set rstTarget = New ADODB.Recordset
    rstTarget.Open "SELECT [ID], [Name], [City] " & _
                   "FROM [Sheet1$]", cnn, adOpenDynamic, adLockOptimistic

    ' Open Customer data recodset
    Set rstCustomers = New ADODB.Recordset
    rstCustomers.Open "SELECT CompanyID, CompanyName, City " & _
                   "FROM Customers " & _
                   "WHERE City = 'Madrid' " & _
                   "ORDER BY CompanyName", CurrentProject.Connection

    ' Open Service Rep data recodset
    Set rstServiceReps = New ADODB.Recordset
    rstServiceReps.Open "SELECT EmployeeID, [LastName] & "", "" & [FirstName] AS Name, City " & _
                    "FROM Employees " & _
                    "WHERE City = 'Madrid' " & _
                    "ORDER BY [LastName]", CurrentProject.Connection

    ' Loop through Customers result set and copy to target
    Do While Not rstCustomers.EOF
        rstTarget.AddNew
        rstTarget![ID] = rstCustomers!CompanyID
        rstTarget![Name] = rstCustomers!CompanyName
        rstTarget![City] = rstCustomers!City
        rstCustomers.MoveNext
    Loop

    ' Insert blank line between result sets.
    rstTarget.AddNew
    rstTarget![ID] = ""
    rstTarget![Name] = ""
    rstTarget![City] = ""
    rstTarget.Update

    ' Loop through Service Reps result set and copy to target
    Do While Not rstServiceReps.EOF
        rstTarget.AddNew
        rstTarget![ID] = rstServiceReps!EmployeeID
        rstTarget![Name] = rstServiceReps!Name
        rstTarget![City] = rstServiceReps!City
        rstTarget.Update
        rstServiceReps.MoveNext
    Loop

    rstTarget.Close
    rstCustomers.Close
    rstServiceReps.Close

    cnn.Close

    If Not boolSuppressMessages Then
        MsgBox "Workbook Created", vbInformation + vbOKOnly, "ExcelExport"
    End If

ExitHere:
  On Error Resume Next
  Set rstTarget = Nothing
  Set cnn = Nothing
  Set rstCustomers = Nothing
  Set rstServiceReps = Nothing
  Exit Sub

ErrorHandler:
  Eval "MsgBox(""Error " & Err.Number & "@" & Err.Description & "@"")"

  On Error Resume Next

  If Not cnn Is Nothing Then
    cnn.Close
  End If

  Resume ExitHere

End Sub



HTH,

Bob

Apologies Jim, got my threads crossed...

Whilst everyone is on this topic, I did post this query but no one has responded as yet. I am using the output to command and have come up with an error which states that the limit of Access or the Output to command has been exceeded. I am using a union query and the no or rows is 21095. What is the limit of the rows and how can I overcome the problem?

Code is:

Set qdfNew = CurrentDb.CreateQueryDef("NewQueryDef", _
                "SELECT * FROM [Adviser Flows - Holdings] WHERE [DbaseMngr] like '" & strFundMngr & "*'" _
                & "union all SELECT * FROM [ADVISER FLOWS - HOLDINGS Essentials Funds] WHERE [DbaseMngr] like '" & strFundMngr & "*'")
          'If there are records for the Fund Manager the data is exported into the Excel file
          If DCount("[DbaseMngr]", "NewQueryDef") <> 0 Then
                        StatusBar "The " & strFileNme & " is being created...."
            DoCmd.OutputTo acOutputQuery, "NewQueryDef", acFormatXLS, strPth & strFileNme

If anyone can help I would really appreciate it.

Thanks
Renee