Automation of Query results Export

[runningonempty]

Hi All

I need to create a query macro which brings up a query form. After entering criteria in form, the query runs, gets you the results and exports it to a new excel file, save as and awaits your input of the new excel file name, after which it defaults back to Access and awaits fresh input into the query form.

Please help!

Thanks

 

[wongm003]

Hi runningonempty, It sounds like you would need to create a query, form and macro. The query would have criteria based off the form fields. The macro would open the form (Action = OpenForm)... from the form you can add this code to a button to export the query.

Private Sub cmdExportQry_Click()
'Make sure you have reference to "Microsoft Excel xx Object Library" in Tools > References
On Error Resume Next

    Const strQryName = "Query1"    'change to the name of your query
    Dim strFileName As String
    Dim strInitName As String
    Dim strFileFilter As String

    'default the file to current directory with name of query
    strInitName = CurrentProject.Path & "\" & strQryName & ".xls"
    strFileFilter = "Excel Files (*.xls), *.xls"
    strFileName = Excel.Application.GetSaveAsFilename(strInitName, strFileFilter)
    
    'if you want the user to modify the excel file change false to true
    DoCmd.OutputTo acOutputQuery, strQryName, acFormatXLS, strFileName, False
End Sub