How do I export multiple queries ?

[michelle copper]

I have codes for export that looks like this

Private Sub cmdExport_Click()
 
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryExportMetrics"
 
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
 
With dlgOpen
  .ButtonName = "Export To"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = CurrentProject.Path
     If .Show = -1 Then
       'Allow for Root Directory selection: C:\, D:\, etc.
       strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
 
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
 
 
 
 
       MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
               conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
     End If
End With
 
'Set the Object Variable to Nothing.
Set dlgOpen = Nothing
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
 
 
End Sub
 
 

and This above code export were only able to export one single query/table.

I would like to export more queries to one single excel when I am exporting.

What should I add to my codes to make that happen?

[NeoPa]

When exporting multiple tables/queries to a single workbook it is necessary to execute the TransferSpreadsheet command multiple times with the same value of FileName. The Tablename value would change each time, and this value is also used to name the resultant Worksheet within the Workbook.

I have no knowledge of which other queries you intend to include, nor how you intend to determine these, so I can't help further at this stage. If you want to add that information into the thread we may be in a better position to help.

[michelle copper]

I have three other queries that I would like to be exported as well and they are:

qryCapacity
qry3
qry4

and I would like the three queries to be exported all together so that when it has exported, everything will be in one single excel file but in all separate worksheet.

I had someone helped me before in one of my threads. He was successful in helping me export all my queries at one time but he set it in a way that there is no option for me to select where I would like to my file to i.e on a desktop, c drive etc.

With the above code, I was able to export only one single query and I have an option as to where to save my exported file to.


Do I add something like this to my existing codes?

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry3")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry4")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCapacity")
 
 

[NeoPa]

Close.

What you need is to add the three commands (after line #21 of your original code), but they all need the FileName parameter set too :

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry3", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry4", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCapacity", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")

[michelle copper]

Great stuff NeoPa! You did it again!!!

[NeoPa]

I try :-)

[michelle copper]

Now that I got this up and running, I have more questions just for future use.
I will post that question to new thread and I hope you will be able to help me again :DDD

[NeoPa]

As far as I am able certainly :-)