Export/Output Open Query to Excel (1 Viewer)

[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.

What I would like to know 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'm a bit of an Access noob and I'm not sure how to do this. I have found a way to export based on query name but this isn't suitable as it will export the named query regardless of which one has been opened/run.

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 Supplement
        DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, strFileSaveName, False
    End If
    If InStr(qdf.Name, "Extra") <> 0 Then  'If query name contains Extra
    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

This will export any query with Supplement and Extra in the name which isn't quite what I'm looking for.

Any help would be greatly appreciated.

Thanks,

Haydn

 

[arnelgp]

After Ur macro runs does the query immediately by shows up and got focus. If that is the case you can grab it's recordsource and create temp query to export.

Dim bar as variant
Var=screen.activedatasheet.recordsource

Then create a temp query Def from var for export.

 

[haydnm]

Hi Arenlgp,

Yes the access query comes up when it has been run and it sounds like your suggestion would work. Unfortunately, as I'm new to Access I have no idea how I would go about doing what you've described. Would you mind providing an example using the code I provided?

Thanks,

Haydn

 

[arnelgp]

I'm not n a computer right now, only using my cp will give u a sample tomorrow if nobody does.

 

[arnelgp]

Here is your sample db

 

[haydnm]

Thanks for the sample arnelgp. Looking through it, the code seems to always export the query as zzquery. I have four queries with four different names and I need to keep these names intact when exporting. How would I do this in the code you have provided?

 

[arnelgp]

its not the zzQuery that is being exported,
its the Query1, Query2, Query3, Query4.
see this example.
if you want to adapt the code, import the 2 Modules
in your db.

 

[haydnm]

Thanks for the assistance arnelgp, much appreciated. I've imported the modules and customised the code and it's working pretty much as I want it to.