Export/Output Open Query to Excel

[haydnm]

Hi,

I have an Access DB that has 4 queries and one form. Depending on what the user does in the form will determine which query is run. This is controlled by a macro that looks at the state of three tick boxes and runs a query accordingly.

My query for the forum is, can I have a single button that will export only the query that has been run? I don't mind if it's a two step process (e.g. run the query from the form then go back to the form and export the open query).

I have found a way to export based on query name but this isn't suitable as it will export the query regardless of which one has been opened/run.

I've looked online for a solution to this but haven't found one that's suitable. Here's my (mostly borrowed) code so far:

Public Sub testexport_click()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim dlgSaveAs As FileDialog 'Create A FileDialog Object
  Dim strFileSaveName As String


        Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs) 'Set it to be the SaveAs FileDialog
        With dlgSaveAs
        .InitialFileName = CurrentProject.Path & "\" & "Direct Booking Search " & Format(Now, "dd-mm-yyyy") & ".xlsx" 'Set the default filename and directory
         .InitialView = msoFileDialogViewDetails 'Set the default folder view
          .Title = "Choose A File Name" 'Set your own dialog title
        End With


  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    If InStr(qdf.Name, "Supplement") <> 0 Then  'If query name contains 2
        DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, strFileSaveName, False
    End If
    If InStr(qdf.Name, "Extra") <> 0 Then  'If query name contains 2
    DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, strFileSaveName, False
    End If
    
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
  MsgBox "Your data has been exported", vbOKOnly
End Sub

Any help would be greatly appreciated.

Thanks

Haydn

 

[alansidman]

Here is code that I use to export a selected query to Excel.
1. I use a combo box to list all the queries
2. I use a command button to run that query and export it to the users Desktop

Private Sub Command5_Click()


    Dim reportName As String
    Dim theFilePath As String
    
    reportName = Me.cmbQuery 'change combo box name to what works for you.
     
    theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
    theFilePath = theFilePath & reportName & ".xlsx" 'change the path to what you prefer.
 
    DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True
    MsgBox "Check your Desktop."
    
End Sub

 

[haydnm]

Thanks for the reply Alansidman but it doesn't help me in my case unfortunately. I don't want the user to choose the query, the form is already doing that for them so just need to export the open query.

 

[baderms]

Create a radio button, not check boxes as you only want to allow them to make on selection, then do the following (going to borrow alansidman's code here to save time)

Private Sub Command5_Click()

    Dim strqry         as string
    Dim theFilePath As String

    Select case radiobutton selection
        case 1
            strqry = query name for this selection
        case 2
            strqry = query name for this selection
        case 3
            strqry = query name for this selection
        case 4
            strqry = query name for this selection
        case else
    end select

    theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
    theFilePath = theFilePath & strqry & ".xlsx" 'change the path to what you prefer.
 
    DoCmd.TransferSpreadsheet acExport, 10, strqry, theFilePath, True
    MsgBox "Check your Desktop."
    
End Sub

 

[xenou]

Depending on what the user does in the form will determine which query is run. This is controlled by a macro that looks at the state of three tick boxes and runs a query accordingly.

Seems like you could just add an additional step (exporting the query) to the macro.

 

[Micron]

Seems like you could just add an additional step (exporting the query) to the macro.

Except all 3 checkboxes (tick boxes??) could be selected, so which is to be exported? I prefer the option group idea. Or maybe I misunderstood your meaning.
haydnm: if you go that route, the option (radio) buttons have to be correctly inserted into an option frame, and the value is passed to the frame. So you have to reference the frame value and not code for the specific radio button.

 

[xenou]

I was assuming the OP was getting the correct query to run (since the only question was how to export the same one that ran).