Results 1 to 14 of 14
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Fastest Way To Export Multiple Queries To ONE Workbook


    I am currently using this to export 4 queries to one Excel workbook, but this takes roughly 5 minutes so I am searching to see if there are better solutions
    Code:
    			DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryOne", "test1.xlsx", True, "A"
    			DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryTwo", "test1.xlsx", True, "B"
    			DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryThree", "test1.xlsx", True, "C"
    			DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryFour", "test1.xlsx", True, "D"

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    nope, that the best.

    It could be the complexity of the queries. But mine run fast for large recs. INstantly for small datasets.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by ranman256 View Post
    nope, that the best.

    It could be the complexity of the queries. But mine run fast for large recs. INstantly for small datasets.
    Thanks for the confirmation - I am exporting roughly 2,000 records per query so total of 8,000 - must be due to the amount of data being export.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    volume of records isn't that great - how long does it take to run each query if you just open them? Also, a) you are specifying a range - a range only works for imports. Populating it for an export the action should fail and b) exporting consecutively to the same spreadsheet may introduce delays.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Ajax View Post
    volume of records isn't that great - how long does it take to run each query if you just open them? Also, a) you are specifying a range - a range only works for imports. Populating it for an export the action should fail and b) exporting consecutively to the same spreadsheet may introduce delays.
    Running each export individually takes a matter of seconds (what I would expect when I run them consecutively) - not sure if it is Excel getting hung-up or something?

    And no - I am not exporting to a range, I am exporting the exact query results to the worksheet.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    And no - I am not exporting to a range, I am exporting the exact query results to the worksheet.
    but your code is specifying a range

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryOne", "test1.xlsx", True, "A"

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    thats a sheet, ("A") , not a range.
    IF the workbook is open, it WILL take a long time,if not fail to export.
    the workbook MUST be closed.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    according to Microsoft:

    Range: (Optional) A string expression that’s a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

    The OP is exporting

    Are you saying that you can specify a sheet for exporting?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You don't have folder path in the spreadsheet naming, where is the export saved to?

    Just did a test and I'll be damned, the range argument works to assign name to worksheet. This allows exporting to same workbook without overwriting worksheet. Learned something new.

    Did a test with tables of 20,000+ and 33,000+ records and took less than 6 seconds each individually from the Immediate window. Tried a table of 66,000+ and the export fails. The workbook is corrupted and won't open. Maybe something about that particular dataset but no idea what it could be, just number, text, date/time fields, same as other tables.

    Tested automated export of the two smaller tables from a procedure and took less than 20 seconds. Exported each twice so there were 4 sequential and still less than 20 seconds. Tried four queries, including a UNION, still about 20 seconds.

    However, workbook created from the sequential procedure opens with 'corruption recovery' prompts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Somebody explain me please, why to export a query from Access to Excel instead querying Access from Excel? In 17 years I have used both, I have never had to do this!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have done both - Access send to Excel and Excel pull from Access.

    Why write code in another file when a simple export will serve user requirement for their individual manipulation of data?

    In different case users need data from Access but those users never work with the Access file, other users do all the input. Excel file has a lot of code to pull and manipulate data which is eventually used as source for import by another proprietary program.
    Last edited by June7; 10-23-2017 at 07:50 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just did a test and I'll be damned, the range argument works to assign name to worksheet. This allows exporting to same workbook without overwriting worksheet. Learned something new.
    and me too

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    Why write code in another file when a simple export will serve user requirement for their individual manipulation of data?
    Do we mean something different with "query" and "code"?

    A query in Excel is an ODBC Query for me. You select from Excel menu Data > From other sources > From Microsoft query, and select MS Access database from list of ODBC drivers. Then you locate your Access (back-end) database, select a table or saved query or tables/queries when you want to use several sources, select fields to read into excel, set some PK to be Not Null, set Order settings, when needed - depending do you have one or several source tables - you are directed into query designer automatically or you can select between query designer and sending data to worksheet, correct the cell address from where data are inserted, before continuing click on Properties button and set the query to be refreshed on opening, and press OK. It's all.

    Whenever the user opens the workbook, the last saved data from access is read into it. Additionally, user can always refresh the query manually (right-click on query range > Refresh).

    For test I created a query to read a table >15 000 rows from Access back-end on network resource, spent a couple of minutes do design it, and data was read in in less than second.

    A code is for me what you write into VBA Project of workbook - mostly into Standard Module. Of-course you can define and run queries here too, but those are very specific cases.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    To each his/her own. In this situation I had to design a very dynamic filtering (user selects different combinations of fields, select AND or OR operator) so the code modifies query with QueryDefs - such a small extra step to just export and open spreadsheet. And Access file had to be distributed to remote offices therefore only 1 file had to be kept track of.

    Excel import could be better approach for jo.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-22-2016, 05:18 PM
  2. Replies: 6
    Last Post: 01-29-2015, 08:53 PM
  3. Replies: 2
    Last Post: 05-16-2013, 07:43 PM
  4. Replies: 1
    Last Post: 03-12-2012, 02:21 PM
  5. Export 2 Queries to Same Workbook in Access 2010
    By Mikey in forum Import/Export Data
    Replies: 2
    Last Post: 08-23-2010, 05:16 AM

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