Results 1 to 2 of 2
  1. #1
    moloff is offline Novice
    Windows XP Access 2000
    Join Date
    May 2009
    Posts
    1

    Combine two queries from forms and export to excel

    Please bear with me. I am well versed in Excel VBA, but not at all familiar with Access. Last year I took over a piece of equipment that collects data from a process. The person who set up the data collection scheme has since moved on and left the company. He set up a couple of queries that are accessed from a form where start and end time are requested. Following that you can press different buttons to pull the data from that experiment. Following the query, the user is asked to go to Tools and Analyze with Excel to get the data into excel. For my work, I do two different queries and add them to one excel spreadsheet followed by some excel macros. I can do this easily, however, I would much prefer to have a macro that runs two queries, and add them both to one Excel work book but two different sheets. Can anyone help me?

    Thanks!

    These are my queries:

    PARAMETERS Forms![Data Extract Form]!Start DateTime, Forms![Data Extract Form]![End] DateTime;
    TRANSFORM Avg(Basic.Val) AS [The Value]
    SELECT Basic.DateAndTime
    FROM Basic
    WHERE (((Basic.TagName) Like "alarms\e_stop_pressed" Or (Basic.TagName) Like "mmi_temps\*") And ((Basic.DateAndTime) Between Forms![Data Extract Form]!Start And Forms![Data Extract Form]!End))
    GROUP BY Basic.DateAndTime


    PIVOT Basic.TagName;


    PARAMETERS Forms![Data Extract Form]!Start DateTime, Forms![Data Extract Form]![End] DateTime;
    TRANSFORM Avg(Basic.Val) AS [The Value]
    SELECT Basic.DateAndTime
    FROM Basic
    WHERE (((Basic.TagName) Like "buttons\*" Or (Basic.TagName) Like "unit\cycle_step" Or (Basic.TagName) Like "unit\mmi_v*") And ((Basic.DateAndTime) Between Forms![Data Extract Form]!Start And Forms![Data Extract Form]!End))
    GROUP BY Basic.DateAndTime
    PIVOT Basic.TagName;

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Try the following code in vba module:

    PS: qXTab1 and qXTab2 are query names you want to export in access

    Code:
    Sub ExportQueryToExcel()
     
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qXTab1", "e:\testquery1.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qXTab2", "e:\testquery2.xls"
     
        Dim xl As Excel.Application
        Set xl = New Excel.Application
        xl.Visible = True
        xl.Application.Workbooks.Open Filename:="E:\testquery1.xls"
        xl.Application.Workbooks.Open Filename:="E:\testquery2.xls"
        xl.Application.Sheets("qXTab2").Move After:=Workbooks("testquery1.xls").Sheets(1)
     
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 AM
  2. Replies: 4
    Last Post: 04-01-2009, 08:49 PM
  3. Export to Excel
    By vaikz in forum Import/Export Data
    Replies: 3
    Last Post: 03-25-2009, 09:37 AM
  4. Newbie Needs help with combine/average
    By wfd1753 in forum Queries
    Replies: 2
    Last Post: 05-16-2008, 06:43 AM
  5. Combine queries results in forms
    By frasilvio in forum Queries
    Replies: 12
    Last Post: 01-10-2008, 01:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums