Results 1 to 5 of 5

Using VBA to export query results to Excel with pre-determined parameters and no forms

  1. #1
    Newlin47 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    3

    Angry Using VBA to export query results to Excel with pre-determined parameters and no forms

    I need assistance creating a VBA code to export the results of a query to Excel using pre-determined parameters and pre-determined file names.

    (With apologies, since I'm an administrator who works with public schools, in accordance with US federal law, I'm very limited in my ability to post files or actual examples...I could only do so with serious redactions).

    One of the ways I'm using my database is to take the disparate files/data from many proprietary government systems and to combine them into useful information--in this case, student-level information about student progress to meeting graduation requirements.

    The query is quite simple: it's a select query that pulls several other query calculations together. But since there are 61 high schools that my office supports, every time I want to export a fresh version, I don't want to individually create 61 different queries with criteria, and I don't want a macro that opens the query, applies a criteria/parameter, exports, then closes the query, etc. since that will make it take much longer than necessary.

    The 61 different high schools can be differentiated by very tight 6 character string that is unique to them, so applying this string criteria or parameter will only return the students enrolled at that school. I want to export these 61 files as Excel files so that my colleagues can make use of them. I'm the only person on the team with knowledge of Access, and for security reasons and other logistical barriers, I cannot currently place the DB in a place where this could be controlled via form.

    So the ideal VBA would apply a parameter to the query in the school identification string (called a DBN) and then export that filtered record set to a pre-determined file path and name. The code DBNs aren't changing anytime soon, and a simplified version of a VBA code would make generating updated versions of these reports much, much easier to produce and share with my team.

    For example, if the DBN for one of the 61 HSs was "99X999", then the ideal VBA code would export the query recordset to a folder on my computer like this "99X999 Graduation Progress Report.xlsx", of course only containing the student information for the "99X999" school.

    I was able to accomplish something similar with a report version of this data, and this is a redacted version (no changes to code, only the school identifying strings) of what I was able to accomplish in VBA by doing the same thing but instead of exporting to Excel, this exports a PDF report of the same data (to created individual student reports for a grade level in a PDF file):



    Code:
    Private Sub ExportNewlinGradProgressPDFs()
    
    
        Dim reportName As String
        Dim fileName As String
        Dim criteria As String
        
        reportName = "Grad Progress and Program Report No Param"
        fileName = "F:\School Reports\89Q404 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q404' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q404 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q404' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q409 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q409' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q409 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q409' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q422 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q422' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q422 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q422' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q502 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q502' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q502 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q502' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q504 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q504' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q504 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q504' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q507 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q507' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q507 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q507' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q583 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q583' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q583 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q583' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q615 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q615' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q615 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q615' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q639 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q639' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q639 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q639' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q659 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q659' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q659 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q659' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q660 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q660' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q660 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q660' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q683 Coh U Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q683' AND [CO YR] = 'U'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
        
        fileName = "F:\School Reports\89Q683 Coh V Grad Progress and Program Report.pdf"
        criteria = "[DBN] = '89Q683' AND [CO YR] = 'V'"
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
    
    
    End Sub
    The above code successfully exports 24 PDF files (2 for each school/DBN) that contain specific students and grade levels using the "CO YR" field and "DBN" field, and does so without a version of the report popping up and being seen by the user. Just click and it works. It re-names the criteria/file name as strings, exports the report, closes the report, then re-sets the criteria, etc.

    But I've been unable to find a similar solution for doing the same thing, but with the recordset/query data of the same information.

    FYI: The query name would be "Graduation Progress Report Query".

    Any insight or help is greatly appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,738
    1. you could build all the queries needed with the report names into a table then run all the queries in the table then export
    QUERY, FILE

    or
    2. use a form to select criteria and export.
    or
    3. or use your way with a lot of code that really isn't needed.

  3. #3
    Newlin47 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    1. you could build all the queries needed with the report names into a table then run all the queries in the table then export
    QUERY, FILE

    or
    2. use a form to select criteria and export.
    or
    3. or use your way with a lot of code that really isn't needed.
    Option 2 isn't viable really, since no other user but me is using the DB and I wouldn't want to use a form to select to export the 61 files to Excel separately or 61x uses of the form.

    Option 3: I'm fine with whatever solution works at the moment!

    Option 1: If I understand you correctly, you're saying I could create a table with text strings that would allow me to quickly generate all the 61 queries/parameters I would need? I wanted to avoid having to create the 61 separate queries/parameters I know I'll need each time for exporting. Would you be able to explain that option in any more detail, or toss a link to a resource my way? I'm new to VBA overall. I just haven't figured out how to efficiently export the filtered/criteria added queries as Excel files with the specific filter entered into the resulting Excel file name (other than creating 61 unique queries, or re-exporting a simple parameter query manually 61 times, etc.).

    The exporting of a report in VBA seems to allow for the inclusion of criteria (see originally posted code) but the exporting for recordsets/queries to Excel seems to not (or it requires that the query be opened, have the parameter applied, exported, closed, then re-opened again, etc., 61 times). I've tried doing it with a very involved macro (with limited success) so I thought VBA would maybe be more efficient (as it was with exporting reports as PDFs).

    Thank you!

  4. #4
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Newlin47,

    I think that this example helps you:
    Code:
    With CurrentDb
        With .QueryDefs("MyQuery")
            fileName = "F:\School Reports\89Q404 Coh U Grad Progress and Program Report.xlsx"
            .SQL = "SELECT * FROM MyTable WHERE [DBN] = '89Q404' AND [CO YR] = 'U'"
            DoCmd.OutputTo acOutputQuery, .Name, acFormatXLSX, fileName
            
            fileName = "F:\School Reports\89Q404 Coh V Grad Progress and Program Report.xlsx"
            .SQL = "SELECT * FROM MyTable WHERE [DBN] = '89Q404' AND [CO YR] = 'V'"
            DoCmd.OutputTo acOutputQuery, .Name, acFormatXLSX, fileName
        End With
    End With
    I suggest to put it in a Loop instead repeat it for each [DBN] code.

    Good luck!

  5. #5
    Newlin47 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    3
    Thanks for the suggestion: I'll give it a try!

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

Similar Threads

  1. Replies: 5
    Last Post: 07-14-2018, 07:22 AM
  2. Export Query Results to Excel Template
    By laterdater in forum Macros
    Replies: 2
    Last Post: 09-25-2015, 11:20 AM
  3. Replies: 20
    Last Post: 03-13-2014, 12:50 PM
  4. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  5. Replies: 1
    Last Post: 04-30-2012, 05:10 PM

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
  •  
Tech Forums: Microsoft Office Forums