Hi Everyone,
I have not solved the problem from my last post. Therefore I am posting again to see if there is anyone out there who would be able to help me.
I have a code/module which exports the query to excel. I am having trouble in exporting pivot tables. I have created pivot tables based on the query. But when I export using the codes below, it exports only all the data but not the pivot tables that I have created. Therefore, what code should I be adding to the existing code to export the pivot tables to excel as well?
The codes that I am using for exporting simple query is:
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
thank you and hope someone will be able to help me.