export pivot table view to excel

[sk88]

so I have this code for exporting all my other queries

Private Sub Command4_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 = "qryGEM"
   
  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")
                          
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                 TableName:="qryPivot", _
                                 FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
           Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                 TableName:="qryPresentingProblem", _
                                 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

The codes work perfectly fine but it doesnt work well for pivot table view.

What should I add to the add to make it work? I dont want pivot table in datasheet.

I did try the codes posted by other people but it works well for if I have only ONE QUERY to export. As you can see, the above codes are for exporting more than one query.

Thank you...

[June7]

Review this http://forums.aspfree.com/microsoft-...el-462681.html

[sk88]

Review this http://forums.aspfree.com/microsoft-...el-462681.html

Thanks June!

I managed to get Pivot tables to export but it won't export more than 1 pivot tables...
I used the below codes for exporting one pivot table and it works perfectly fine.

Private Sub Command0_Click()

DoCmd.OpenQuery "qryProblem", acViewPivotTable, acEdit
DoCmd.RunCommand acCmdPivotTableExportToExcel
DoCmd.Close acQuery, "qryProblem"



End Sub

I have more than one pivot tables and queries.. How do I go about this?
Can I somehow combine the codes with my first post codes?

thanks!

[June7]

Did you repeat the code for each query? Why won't export more than one pivot table? Error message, wrong results, nothing happens?