Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Exporting queries to .xlsx

    I have a form in my database that it used to run various reports. There's essentially a separatre report for each of our internal systems/applications - the report is an output of a query that pulls a list of users with access to that system, their assigned role, job title, etc.
    Users first select the system/application from a List Box in the form, and then click an "Export" button.
    Then, I'm using the "DoCmd.OutputTo" function so that the output of that query automatically opens up within an Excel spreadsheet on the user's PC.

    Currently, the queries are all declared/set in the "On Click" event behind the "Export" button in the form. I then have a series if If..ElseIf.. statements to determine which query should be outputted (based upon the system that they selected from the List Box on the form). Below is an snippet of that code.


    I assume there must be a more elegant & efficient way to do this!!!

    Code:
    Private Sub btnExport_Click()
    
    Dim strQuerySys1 As String
    Dim strQuerySys2 As String
    Dim strQuerySys3 As String
    Dim strQuerySys4 As String
    Dim strQuerySys5 As String
    Dim strQuerySys6 As String
    Dim strDate As String
    Dim strUser As String
    Dim currentHour As String
    Dim currentMinute As String
    Dim FileNameWithTime As String
    
    strDate = Date
    strUser = Environ("Username")
    currentHour = Hour(Now)
    currentMinute = Minute(Now)
    FileNameWithTime = currentHour & currentMinute
    
    strQuerySys1 = "qryUsers_With_Access_To_Sys1"
    strQuerySys2 = "qryUsers_With_Access_To_Sys2"
    strQuerySys3 = "qryUsers_With_Access_To_Sys3"
    This part goes on several more lines...

    Then, the If..ElseIf statements (which I should probably update to use the Select Case function if nothing else)

    Code:
    If IsNull(Me.listUAR.Column(0)) = True Then
    
    
    MsgBox "u need to select a system from the list before the report can b run.", vbCritical
      
    End If
            
    If Me.listUAR.Column(0) = 1 Then    
    
    
        DoCmd.OutputTo acOutputQuery, strQuerySys1, acFormatXLSX, "User_Access_" & strUser & FileNameWithTime & ".xlsx"
    
    
    ElseIf Me.listUAR.Column(0) = 2 Then   
    
    
        DoCmd.OutputTo acOutputQuery, strQuerySys2, acFormatXLSX, "User_Access_" & strUser & FileNameWithTime & ".xlsx"
    And again, this goes on for several more lines.. There's 13 distinct options from the List Box on the form - so I have 13 queries defined for each one.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    vFile = "c:\folder\myFile.xlsx"
    vQry = "qsExport2Excel"
    vSheetName = "myQuery1"

    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it conceivable to modify the listbox row source to include the query name for each selection (can be a hidden column)? If so, you can just get it from there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 7
    Last Post: 10-07-2016, 02:42 PM
  2. Replies: 7
    Last Post: 09-21-2014, 11:32 AM
  3. Exporting two queries into separate sheets on one spreadsheet
    By sam.eade in forum Import/Export Data
    Replies: 2
    Last Post: 07-07-2014, 09:53 AM
  4. Exporting 3 queries to 3 textfile
    By Grooz13 in forum Import/Export Data
    Replies: 4
    Last Post: 10-05-2010, 09:15 AM
  5. Exporting Access queries to Excel
    By dbDamo in forum Import/Export Data
    Replies: 2
    Last Post: 09-22-2009, 01:42 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