Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 18th, 2004, 09:50 PM
Registered User
 
Join Date: Jul 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exporting to Excel - one sheet for many queries

Can anyone help me with something I am finding a little 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?


 
Old July 19th, 2004, 09:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

quote:
--------------------------------------------------------------------------------
Each query contains its own information so they cant be combined into one
--------------------------------------------------------------------------------



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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting data to excel sheet x_ray VB.NET 2002/2003 Basics 0 January 6th, 2006 03:14 PM
write multi-sheet Excel w/o Excel.Application manmoth Classic ASP Components 2 November 22nd, 2005 10:56 AM
Query data and exporting data from one sheet to an testman Excel VBA 2 April 28th, 2005 02:40 PM
Exporting data from a repeater to an Excel sheet see07 ASP.NET 1.x and 2.0 Application Design 7 January 17th, 2005 03:46 PM
Exporting data from MS Excel sheet to Ms Access ajindal General .NET 1 January 17th, 2005 03:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.