Results 1 to 11 of 11
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question Export to Excel; One query, multiple tabs by unit

    I'm sure this has been asked a million times, but I can't seem to get the search parameters correct to find information applicable to my situation.

    I have a database of camp attendee information. The attendees are put into units. My director wants an Excel file with each unit on a separate tab and each tab named for the unit. There is no consistency on the number of people in each unit ... 10 to 55 people per unit.

    So far, my process has been to export the data into Excel and then cut and paste the information and rename the tabs. I'm nearly positive this could be automated.

    Assume the query contains:
    FirstName
    LastName
    Type <<-- this is whether the attendee is an Adult or Girl ("A" or "G")


    UnitID <<-- this is the unit number, it is a numerical field (1, 2, 3, etc.)

    I want all of Unit 1 on a tab that is named "Unit 1"; all of Unit 2 on a tab named "Unit 2," etc. The attendees need to be sorted with the adults at the top and then by last name. I now how to do all the sorting and stuff, that's not a problem.

    When I search for "export access query to multiple excel worksheets," I get things like putting five different queries each on their own tab.

    I'm able to use VBA code at a novice level, I just can't wrap my head around this.

    I assume I'd want code that runs a query and cycles through the unit numbers. Oh, the unit numbers run from 0 (the admin unit) to about 23 and then jump to 40 and then to 50. Ages ago, I made the two oldest units 40 and 50 so they would always be the same number each year. The "about 23" varies each year based on the number of attendees. If using 0 for the admin unit creates problems, I can always make it Unit 51 or something like that.

    Then, in the TransferSpreadsheet line, there would be a way to use a string to name the tabs ... "Unit " & [UnitID] ... or some such thing.

    Can someone point me in the right direction?

    Thank you!

    Susie

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    TransferSpreadsheet can export a table or query and the worksheet tab gets the name of the table or query exported. To do more elaborate manipulation of worksheet will require Excel automation code. Here is a site that might help you get started http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    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
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    I didn't want you to think I was ignoring you!

    I am going through the code, trying to understand the commands and syntax. I will post more when I either make it work ... or ... more likely ... Have questions!

    Thank you,
    Susie

  4. #4
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Alright, I have a question.

    At what point does the code execute the SQL behind the temporary query?

    Here is the code from the website above: (I didn't put it as embedded code so I could show my thought process. You can get the code from the website above, it's the third set of code.)

    The black text is the code, green is the comments from the author, red is my comments, bold dark red are my questions, bold blue is another question.

    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim rstMgr As DAO.Recordset
    Dim strSQL As String, strTemp As String, strMgr As String

    ' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
    ' filename without the .xls extension
    ' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)

    Const strFileName As String = "PutEXCELFileNameHereWithoutdotxls" <<--sets the strFileName to be
    "PutEXCELFileNameHereWithoutdotxls"
    Const strQName As String = "zExportQuery" <<--sets the strQName to be "zExportQuery"

    Set dbs = CurrentDb

    ' Create temporary query that will be used for exporting data;
    ' we give it a dummy SQL statement initially (this name will
    ' be changed by the code to conform to each manager's identification)

    strTemp = dbs.TableDefs(0).Name <<--- This I'm not sure I understand. I think it's getting a list of the tables
    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" <<-- I think this is selecting all the tables with a certain attribute, but i don't know what the attribute is
    Set qdf = dbs.CreateQueryDef(strQName, strSQL) <<-- This creates a query named "zExportQuery" with the SQL of
    "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
    qdf.Close <<-- this closes the query
    strTemp = strQName <<-- This sets strTemp to be "zExportQuery"
    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID and EmployeesTable need to
    ' *** be changed to your table and field names
    ' Get list of ManagerID values -- note: replace my generic table and field names
    ' with the real names of the EmployeesTable table and the ManagerID field

    strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;" <<-- This changes the strSQL to select all the unique manager IDs from the EmployeesTable
    Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) <<-- The creates a recordset of the manager IDs

    ' Now loop through list of ManagerID values and create a query for each ManagerID
    ' so that the data can be exported -- the code assumes that the actual names
    ' of the managers are in a lookup table -- again, replace generic names with
    ' real names of tables and fields

    If rstMgr.EOF = False And rstMgr.BOF = False Then <<--- this line and the next line make sure the process is starting at the beginning of the recordset
    rstMgr.MoveFirst
    Do While rstMgr.EOF = False <<-- Run the loop as long as the recordset is not at the end of the list
    ' *** code to set strMgr needs to be changed to conform to your
    ' *** database design -- ManagerNameField, ManagersTable, and
    ' *** ManagerID need to be changed to your table and field names
    ' *** be changed to your table and field names

    strMgr = DLookup("ManagerNameField", "ManagersTable", _
    "ManagerID = " & rstMgr!ManagerID.Value) <<-- this and the line above set the strMgr to equal the name of the manager in the ManagersTable based on the ManagerID
    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID, EmployeesTable need to
    ' *** be changed to your table and field names

    strSQL = "SELECT * FROM EmployeesTable WHERE " & _
    "ManagerID = " & rstMgr!ManagerID.Value & ";" <<--This and the line above change strSQL to select all the employees that have the ManagerID that is the current value in the recordset
    Set qdf = dbs.QueryDefs(strTemp) <<-- I'm not entirely sure what this is doing, I think it's setting the qdf to be the query name of "zExportQuery"
    qdf.Name = "q_" & strMgr <<--Again I'm not sure what this is doing, I think it's setting the qdf name to be Q {underscore} name of the Manager
    strTemp = qdf.Name <<--This changes strTemp to equal the name one line above
    qdf.SQL = strSQL ​<<-- This sets the SQL behind the qdf to be the SQL that selects the employees based on ManagerID

    When I tried code that set my strSQL to update a test table, my code was:
    (assume correct Dim statements)
    strTemp = "123Test"
    qdef was dbs.CreateQueryDef(strTemp)
    qdef.Close
    strSQL = **update table blah blah**
    qdef.SQL = strSQL
    qdef.Execute <<-- This line HAD to be in my code or the query didn't run ... it got created, but didn't run, then got deleted. So where in the code from the author is the query SQL actually run??
    qdef.Close

    set qdef = Nothing
    dbs.QueryDefs.Delete (strTemp)
    dbs.close
    set dbs + Nothing

    qdf.Close <<-- This closes the query
    Set qdf = Nothing<<-- This changes qdf to be nothing

    ' Replace C:\FolderName\ with actual path
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strTemp, "C:\FolderName" & strFileName & ".xls" <<--This and the line above export the query to a spreadsheet named
    "PutEXCELFileNameHereWithoutdotxls.xls"
    rstMgr.MoveNext <<-- This moves to the next ManagerID in the recordset
    Loop <<--This goes back to the DLookup line
    End If <<-- This exits out of the If/Then statement when the recordset is at the end of the file

    rstMgr.Close <<--Closes the recordset
    Set rstMgr = Nothing <<--erases the recordset

    dbs.QueryDefs.Delete strTemp <<--Deletes the query named "zExportQuery" Except I had to put my query title in parenthesis
    dbs.Close
    Set dbs = Nothing

    Thank you very much for taking the time to help me!

    Susie
    Kansas

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code is not intended to create an action query object, just a SELECT query. Then code exports that query object to Excel and eventually deletes the query object.
    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.

  6. #6
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Thank you!

    What do the following two lines do?

    strTemp = dbs.TableDefs(0).Name
    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"



    Susie
    Kansas

  7. #7
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    OK,

    I've got my code written and I'm in the process of debugging ...

    if a recordset is created, but the code hits a snag before the recordset is closed, what happens?

    My code ran up to a certain point (I had a typo) and now I'm getting a Run-time error 3022. it says it would create a duplicate value in the index, primary key, or relationship.

    I DO delete the temporary query each time I have to re-run the code, since it doesn't reach the VBA to delete the query.

    I'm only supposing the recordset it the issue, I don't know.

    Thank you!

    Susie

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    strTemp = dbs.TableDefs(0).Name <<Sets strTemp variable to the name of the alphabetically first table in tables collection.

    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
    <<Builds an SQL statement with the table name.

    The recordset not closing should not be cause of error 3022. What line triggers the error?
    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.

  9. #9
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    How do I post my code so it shows up in the nice scroll-able box?

    Susie

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use CODE tags. Click the # icon and post code between the tags.
    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.

  11. #11
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    IT WORKED!

    I'm doing the happy dance!

    I commented out the line that wasn't working, just to see what happened and then figured out a directory path was wrong and ... it worked!

    THANK YOU!

    Susie
    Kansas

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

Similar Threads

  1. Replies: 2
    Last Post: 05-16-2013, 07:43 PM
  2. Replies: 12
    Last Post: 12-17-2012, 12:47 PM
  3. Export Queries to Excel tabs with Save as Dialog box
    By deepanadhi in forum Import/Export Data
    Replies: 1
    Last Post: 05-16-2012, 10:36 AM
  4. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 01:33 PM
  5. export report to multiple tabs
    By mws5872 in forum Import/Export Data
    Replies: 0
    Last Post: 06-30-2009, 03:07 PM

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