Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Exporting multiple query results to Excel

Discussion in 'Information Technology' started by Mark, Jul 28, 2006.

  1. Mark

    Mark
    Expand Collapse
    Guest

    Hi there,

    I have a database to help with billing. There are something like 90
    queries set up to extract records. I need to export the results of all
    the queries to a single excel file so that each query appears on a
    separate sheet within the file. Doing this manually is a horror show.

    Is there a way to select more than one query at a time and export them
    all together.

    Any assistance or ideas would be much appreciated.

    regards

    Mark


    --
    Mark
    Posted via http://ms-os.com Forum to Usenet gateway
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    yes
    Dim FName as String
    Dim Qdef AS DAO.QueryDef

    Fname="C:\TEMP\Totals.xls"
    For Each QDef In Access.CurrentDB
    Access.DoCmd.TransferSpreadsheet Access.acExport,
    Access.acSpreadsheetTypeExcel97, QDef.Name, Fname, True
    Next

    Pieter

    "Mark" <Mark.2b54sa@no-mx.gateway.localhost> wrote in message
    news:Mark.2b54sa@no-mx.gateway.localhost...
    >
    > Hi there,
    >
    > I have a database to help with billing. There are something like 90
    > queries set up to extract records. I need to export the results of all
    > the queries to a single excel file so that each query appears on a
    > separate sheet within the file. Doing this manually is a horror show.
    >
    > Is there a way to select more than one query at a time and export them
    > all together.
    >
    > Any assistance or ideas would be much appreciated.
    >
    > regards
    >
    > Mark
    >
    >
    > --
    > Mark
    > Posted via http://ms-os.com Forum to Usenet gateway
    >
     
  4. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Mark,

    You can do it with some simple VBA code, looping through each query in
    the QueryDefs collection. and exporting each in turn, like:

    Sub Export_Queries()
    Dim strWorkBook As String
    Dim qdf As QueryDef
    strWorkBook = "C:\SomeFolder\MyFile.xls"
    For Each qdf In CurrentDb.QueryDefs
    If Left(qdf.Name, 3) <> "~sq" Then
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, _
    qdf.Name, strWorkBook, True, qdf.Name
    End If
    Next
    End Sub

    This will export all saved queries in a single workbook identified in
    strWorkBook, with each query on a separate sheet named after the query.

    Alternatively, you could:

    (a) use a standard prefix in the queries you want to export, for
    instance qexpASomething, qexpBlahBlah etc, and change your filter to:
    If Left(qdf.Name, 4) = "qexp" Then

    or,

    (b) use a single field table to hold the names of the queries to be
    exported, and in your code open it as a recordset and loop through
    exporting each, instead of looping through the QueryDefs collection.

    HTH,
    Nikos
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    yes
    Dim FName as String
    Dim Qdef AS DAO.QueryDef

    Fname="C:\TEMP\Totals.xls"
    For Each QDef In Access.CurrentDB
    Access.DoCmd.TransferSpreadsheet Access.acExport,
    Access.acSpreadsheetTypeExcel97, QDef.Name, Fname, True
    Next

    Pieter

    "Mark" <Mark.2b54sa@no-mx.gateway.localhost> wrote in message
    news:Mark.2b54sa@no-mx.gateway.localhost...
    >
    > Hi there,
    >
    > I have a database to help with billing. There are something like 90
    > queries set up to extract records. I need to export the results of all
    > the queries to a single excel file so that each query appears on a
    > separate sheet within the file. Doing this manually is a horror show.
    >
    > Is there a way to select more than one query at a time and export them
    > all together.
    >
    > Any assistance or ideas would be much appreciated.
    >
    > regards
    >
    > Mark
    >
    >
    > --
    > Mark
    > Posted via http://ms-os.com Forum to Usenet gateway
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4285 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  6. Mark

    Mark
    Expand Collapse
    Guest

    Many thanks gentlemen. I'll give it a try and let you know how i get
    on.

    regards

    Mark


    --
    Mark
    Posted via http://ms-os.com Forum to Usenet gateway
     
  7. Mark

    Mark
    Expand Collapse
    Guest

    Hi Nikos

    Tried your code and keep getting

    "Run time error 3011

    the microsoft jet engine could not find the object "David" (name of
    query). Make sure the object exists and that you spell it's name and
    path correctly."

    I've tried running this several times and always get the same response.
    I then set up a test database with only five queries in it and with
    simpler names to see if that was the problem.

    Lastly i tried renaming the sheets in the target excel file to match
    the queries, but still no joy. Am I missing something really simple.

    Many thanks

    regards

    Mark


    --
    Mark
    Posted via http://ms-os.com Forum to Usenet gateway
     

Share This Page