Results 1 to 4 of 4
  1. #1
    bliffer is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    2

    Export one query to multiple Excel files

    I can't seem to find much to help me out on this at all but here's my goal:



    I have one query that I need to export to multiple Excel files. What I would like to do is create a table that has one column that would hold the criteria for the WHERE clause of my query. Then, I would like to automatically export one Excel file for each entry in the criteria table.

    Basically what I have is a giant file that has 400+ values in a certain column. I've been asked if I could create one spreadsheet with each of the 400 values. That would be a bear to do manually!

    Any ideas?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    The 400+ values are the filter criteria? Post an example.

    Several ways to accomplish. Following is possibly the simplest. Open a form bound to the table with the 400+ values, code can move to each record on form. Query object refers to textbox bound to 400+ field as a filter parameter. Code exports query object with TransferSpreadsheet method.
    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.

  3. #3
    bliffer is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    2
    Quote Originally Posted by June7 View Post
    The 400+ values are the filter criteria? Post an example.

    Several ways to accomplish. Following is possibly the simplest. Open a form bound to the table with the 400+ values, code can move to each record on form. Query object refers to textbox bound to 400+ field as a filter parameter. Code exports query object with TransferSpreadsheet method.
    Thanks for the response!

    The query would have a clause like "...WHERE CHAINCODE = '[Value]'"

    The table would hold all of the values and the automation would run one query per value on the table and then export to an Excel file and name it something like "[CHAINCODEValue]-GenericFileName.xlsx"

    My problem is that I am horrible at looping functions so if anyone knows of a tutorial that shows how to go about this I would appreciate it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    So the criteria is not applied to the same field for each case? That does complicate. Other approaches:

    1. code modifies query object using QueryDefs collection

    2. code writes records to a 'temp' table and then export the table

    3. code opens Excel object and manipulates to add data one value at a time then save the workbook

    Any solution requires looping code. There are many tutorials to learn the many facets of programming but no one source will tell you how to do EXACTLY what is required for your situation..
    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: 3
    Last Post: 01-07-2014, 03:39 PM
  2. Replies: 4
    Last Post: 12-05-2013, 09:32 AM
  3. Replies: 11
    Last Post: 12-20-2012, 12:30 PM
  4. Export Access into multiple excel files based on field value
    By turntabl1st in forum Import/Export Data
    Replies: 7
    Last Post: 11-08-2012, 12:43 PM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 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