Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Export to XL spreadsheet per site (A2K)

    Looking for reference to a snippet of code (macro?) that will quickly export to an XL workbook/spreadsheet for each id in the data.

    Client has 35 sites, and routinely exports lists of data to spreadsheets oe for each site.

    Does anyone have ideas for routinizing th is process.

    The staff want spreadsheets, reports cause them problems, even with snapshot viewer, plus there is the fact that reports run like mud connecting to Oracle* via ODBC.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Export to XL spreadsheet per site (A2K)

    Here is a VBA routine that may serve as a starting point. It uses DAO, so you need to set a reference to the Microsoft DAO x.x Object Library in Tools/References...
    It will export to Excel 97 format; if you want a different format, replace the constant acSpreadsheetTypeExcel97 in the DoCmd.TransferSpreadsheet instruction.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub ExportXL(strSource As String, strField As String, strDestination As String)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strGetID As String
    Dim strSQL As String
    Dim strName As String
    Dim lngID As Long

    On Error GoTo Err_Handler

    Set dbs = CurrentDb
    strGetID = "SELECT DISTINCT " & strField & " FROM " & strSource
    Set rst = CurrentDb.OpenRecordset(strGetID, dbOpenForwardOnly)
    Do Until rst.EOF
    lngID = rst(strField)
    strName = "qryTemp" & lngID
    strSQL = "SELECT * FROM " & strSource & " WHERE " & strField & " = " & lngID
    Set qdf = dbs.CreateQueryDef(strName, strSQL)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strName, strDestination, True
    dbs.QueryDefs.Delete strName

    On Error Resume Next
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    Exit Sub

    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    The procedure has three arguments:
    strSource is the name of the table or query you want to export.
    strField is the name of the ID field you want to split the table/query on.
    strDestination is the name of the Excel file to be created. There will be a worksheet for each ID in this file.


    ExportXL "qryMonthlyReport", "ID", "C:ReportsExportedReport.xls"

    will create a spreadsheet C:ReportsExportedReport.xls with a separate worksheet for each ID in qryMonthlyReport.

Posting Permissions

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