Results 1 to 6 of 6
  1. #1
    RedNumber is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3

    Set Query Parameter in VBA before Output to Excel

    Simplified Query1 SQL Statement
    Code:
    PARAMETERS ID Short;
    SELECT OpeningName, OpeningBuilding
    FROM tblOpening
    WHERE OpeningID = [ID];
    Simplified VBA Code Exporting Saved Query to Excel with Parameter Set
    Code:
    1   Private Sub ExportFile()
    2       Dim qdfExport As QueryDef
    3   
    4       Set qdfExport = CurrrentDb.QueryDefs("Query1")
    5    
    6       qdfExport.Parameters(0) = 5
    7
    8       DoCmd.OutputTo acOutputQuery, qdfExport.Name, acFormatXLSX, , True, "", , acExportQualityPrint
    9   End Sub
    Hello All.


    I have a saved query called "Query1" which has one parameter of type Short called "ID." I would like to set the parameter within VBA before the query is exported to an excel file. Eventually I will let the user select or type the value on the form, but for the purposes of this question I am setting the value explicitly within the code.

    My problem is after the save dialogue box opens for the excel file, Access is still asking for the value for the parameter even though once I get down to line 8 in testing, the Immediate Window shows me that qdfExport.Parameters(0).Name is ID and qdfExport.Parameters(0).Value is 5.

    Everything I have found online has given me a similar code layout to what you see here, and I have tried all of the similar combinations of code to this one with the same results. Is there any way of setting the parameter to a query within VBA so that the user is not prompted to enter a value before saving the excel file.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think that would work opening a recordset on the query, not sure it works with OutputTo. Have you considered using a form to gather the user input? Most of us do that anyway, as it gives you more control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RedNumber is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Thanks for the reply.
    I have tried using OpenRecordSet but was unable to use the result with DoCmd.Outputto. I'll look into that option more though.

    Right now instead of saving the Query to the database I have the SQL string in the VBA code to which the parameters are passed from the form, only not as parameters but an actual part of the string. I then create a temporary Querydef using the string, Output the temporary Querydef to Excel, then delete the temporary Querydef as shown below.

    Simplified VBA Code for current practice.
    Code:
    Private Sub ExportFile()
        Dim fileName AS String
        Dim sqlStatement AS String
    
        fileName = "BarrierPenetrations"
        sqlStatement = "SELECT OpeningName, OpeningBuilding " & _
                               "FROM tblOpening " & _
                               "WHERE OpeningID = " & txtOpeningID
    
    
        Dim qdfExport As QueryDef
        Set qdfExport = CurrentDb.CreateQueryDef(fileName, sqlStatement)
    
    
        DoCmd.OutputTo acOutputQuery, fileName, acFormatXLSX, , True, "", , acExportQualityPrint
        CurrentDb.QueryDefs.Delete qdfExport.Name
    End Sub
    This works fine, but these SQL strings can get pretty long and whenever changes are needed it can sometimes be a chore making changes within the code or creating a query, copying code to the query, removing the VBA and then copying it back to the vba after the changes have been made.

    In addition to more easily being able to make changes to SQL statements when necessary, if I am able to store the queries and set parameters within the code, I am thinking I can more easily create subroutines that can handle all of my exporting needs. Unfortunately I can't just let users enter parameters using Access' prompts because I need to perform tests on their inputs before exporting.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, with a saved query with form parameters, you can simply export it. To use a recordset, you'd have to use Excel automation and CopyFromRecordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RedNumber is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    While I couldn't find a way to get the VBA code from the first post to properly set parameters without prompts, I found a different method that is achieving this.

    Query1 SQL Statement with multiple parameters
    Code:
    PARAMETERS BeginDate DateTime, EndDate DateTime;
    SELECT OpeningName, OpeningBuilding
    FROM tblOpening
    WHERE OpeningInspectionDate BETWEEN [BeginDate] AND [EndDate];

    VBA Code Setting Saved Query Parameters and Exporting to Excel without Prompts
    Code:
    Private Sub ExportFile()
         DoCmd.SetParameter "BeginDate", "#" & Format(txtBeginDate, "mm/dd/yyyy  hh:nn") & "#"
         DoCmd.SetParameter "EndDate", "#" & Format(txtEndDate, "mm/dd/yyyy  hh:nn") & "#"
         DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly
         DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, , True, "", , acExportQualityPrint
         DoCmd.Close acQuery, "Query1"
    
    End Sub
    One minor drawback to this method is Query1 must be opened in the process, and there is no acHidden option for opening queries. Another thing to note is there is no short date datatype for parameters, so when passing dates to the parameter, they must be converted to date and time first.

    I am going to consider this problem solved, but I am still curious about the technique involving my first example using qdfExport.Parameters(0). I have seen several people post that method and claim that it worked so if anyone has any information or idea why it might not have been working for me I'd appreciate it.

    Lastly, if anyone has any thoughts on a comparison between the two methods for exporting queries to excel, writing the SQL statements in VBA vs parameterizing saved queries, I'd appreciate those as well.

    I'll be testing and comparing them and will let you know if I find anything interesting.

    Thanks

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You seem to be jumping through hoops rather than use form references in the query, which would be very simple. I don't think your first method would work, because you're not using the QueryDef object in the OutputTo. Offhand I don't think you can, because OutputTo wants a named query or table. I've used that type of code many times, but to open a recordset on the query, which you can do with the QueryDef. I then use Excel automation to push the recordset data to Excel.
    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. Parameter query output to form
    By hasit86 in forum Forms
    Replies: 1
    Last Post: 01-13-2013, 01:23 PM
  2. Output to Excel from VBA
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 10-08-2012, 03:33 PM
  3. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  4. Replies: 3
    Last Post: 04-27-2011, 06:34 PM
  5. URGENT HELP: output to excel
    By jerryrs in forum Import/Export Data
    Replies: 1
    Last Post: 12-22-2010, 12:18 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
  •  
Other Forums: Microsoft Office Forums