Hi all,
I am wondering if anyone can help me figure out what I should be doing in order for me to export multiple queries to one single excel file.
Currently, I have this EXPORT button in my main form where when I click on it, it ask me to save the file/export to my computer. The exported file thus consists of only ONE queries but I wanted more queries to be exported in different worksheet under the same excel file.
So far my codes are:
Public Sub ExportXLS()
#If Not CC_Debug Then
On Error GoTo ErrProc
#End If
Const cQuery As String = "qryExportMetrics"
Dim fc As FileChooser
Dim strFileName As String
Set fc = New FileChooser
fc.DialogTitle = "Select file to save"
fc.OpenTitle = "Save"
fc.Filter = "Excel Files (*.xls)"
strFileName = Nz(fc.SaveFile, "")
Set fc = Nothing
' If user selected nothing or canceled, quit
If Len(strFileName) = 0 Then
Exit Sub
' If file already exists, delete it
ElseIf Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
cQuery, _
strFileName, _
HasFieldNames:=True
ExitProc:
Exit Sub
ErrProc:
ErrMsg Err, Err.Description, Err.Source
Resume ExitProc
End Sub
Any feedback?
Thanks in advance