Exporting Access Pivot tables to Excel

[michelle copper]

Hi everyone,

I need your help. I have a code which exports the query to excel but how do I add or in fact what code should I be adding to the existing code for me to export the pivot tables to excel as well?

My code so far 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

[ADezii]

The following Code will Export ALL Data within Pivot Tables to individual Excel Spreadsheets. Specify the Base Path within the Constant conBASE_PATH, and the Table Name & .xls will be Appended to it. Be sure to substitute your own Table Names in place of the Demo Names. Is this what you are looking for?

Dim astrPivotTables As Variant
Dim bytTblCtr As Byte
Const conBASE_PATH As String = "C:\Exports\"
 
'Populate an Array with ALL the Pivot Table Names
astrPivotTables = Array("Customers", "Orders", "Order Details")
 
'Export ALL Pivot Tables to their individual Spreadsheets
For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
                            conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
Next

[michelle copper]

Hi ADezii,

Thank you so much for your reply.
I am still having problem with the codes you have given me.
Sorry...I am very new to Access. The database which I am working on is built by one of the programmer who left the office.
Do I put your code like this?

Public Sub ExportXLS()
 
#If Not CC_Debug Then
On Error GoTo ErrProc
#End If
 
Dim astrPivotTables As Variant
Dim bytTblCtr As Byte
Const qryExportMetrics As String =&nbs
p;"C:\Exports\"
 
 
'Populate an Array with ALL t
he Pivot Table Names
 
astrPivotTables = Array("MetricsID", "Activity", "Months")
 
 
'Export ALL Pivot Tables to t
heir individual Spreadsheets
 
For bytTblCtr = LBound(astrPivotTables)
 To UBound(astrPivotTables)
 
  DoCmd.TransferSpreadsheet acExport,&n
bsp;acSpreadsheetTypeExcel9, astrPivotTables(bytTb
lCtr), _
 
 
         &
nbsp;        &n
bsp;conBASE_PATH & astrPivotTables(bytTbl
Ctr) & ".xls", True
 
 
 
 
ExitProc:
    Exit Sub
ErrProc:
    ErrMsg Err, Err.Description, Err.Source
    Resume ExitProc
 
    End Sub
 

and yes you are right... I wanted to export the data of the query in one sheet and the pivot tables in other spreadsheet.

[ADezii]

Michele, I do not fully understand the Syntax in Code Lines 9, 10, 31 and 32. Kindly explain.

Line # 9: Const qryExportMetrics As String =&nbs 
Line #10: p;"C:\Exports\"
Line #31: nbsp;        &n 
Line #32: bsp;conBASE_PATH & astrPivotTables(bytTbl 

[michelle copper]

ADezii,
I apologize for the mistake. I do not know how when I copy and paste the codes, it appeared to be different in the site. :S

This is what I have been trying and it is giving me error:

Public Sub ExportXLS()
 
#If Not CC_Debug Then
On Error GoTo ErrProc
#End If
 
Dim astrPivotTables As Variant
Dim bytTblCtr As Byte
 
Const qryExportMetrics As String = "C:\Exports\"
 
'Populate an Array with ALL the Pivot Table Names
astrPivotTables = Array("Activity", "Visit Date")
 
'Export ALL Pivot Tables to their individual Spreadsheets
For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
                            conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
Next
 
ExitProc:
    Exit Sub
ErrProc:
    ErrMsg Err, Err.Description, Err.Source
    Resume ExitProc
 
End Sub

I have also attached two image here regarding the query which I would like to export.
I basically need to export the pivot tables and the data of 'qryExportMetrics'

Thank you so much again for your help. I really appreciate it.

** Edit **

[ADezii]

'Check and see if have have a similar type Statement somewhere in your code:

#Const CC_Debug = True

then try the following and see if it works:

Public Sub ExportXLS()
#If Not CC_Debug Then
  On Error GoTo ErrProc
#Else
  On Error GoTo ExitProc
#End If
 
Dim astrPivotTables As Variant
Dim bytTblCtr As Byte
 
Const conBASE_PATH As String = "C:\Exports\"
 
'Populate an Array with ALL the Pivot Table Names
astrPivotTables = Array("Activity", "Visit Date")
 
'Export ALL Pivot Tables to their individual Spreadsheets
For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
                            conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
Next
 
ExitProc:
  Exit Sub
 
ErrProc:
  MsgBox Err.Description, Err.Source
    Resume ExitProc
End Sub

[NeoPa]

Michelle, see [code] Tags Must be Used for help with the tags. Don't post again without using them. If you're still confused after following the link then feel free to PM me for more help.

PS. Check out When Posting (VBA or SQL) Code to save wasting time unnecessarily.

[michelle copper]

Thanks NeoPa! That was good info!

[michelle copper]

Adezii,

I tried those codes. It seems like there is an error with my pivot tables. I have attached an image again.

** Edit **

[ADezii]

Michele, at this point there makes no sense going back and forth, and basically getting nowhere. If the Activity Table actually exists, I see no logical reason for the Error. Can you send me a sanitized Version of the Database so that I can see it first hand?

[michelle copper]

sure! I will PM you.
Thanks

[michelle copper]

I can't seemed to make attachment for PM. Anyways, I have attached a copy in here. The only problem I am having so far is not being able to export the Pivot Tables view.
I tried creating a form for exporting but it did not really help as well.

[ADezii]

1. Sorry Michele, I do not have Access 2003. Can you Convert the DB to this Version (20030 and Re-Attach?
2. Are you trying to Export the Pivot Tables View, or the Pivot Tables themselves?

[michelle copper]

sure.. I have attached two copies now..one in version 2002-2003 and one in 2000..

Yes..I am actually trying to export the pivot tables view as well as the raw data. Once you see my Access, I am sure you will understand what I am actually trying to do.

Thanks again

[ADezii]

As far as Exporting the Data in qryExportMetrics, the following adjustments will do it. To Export the actual Pivot Table View will be more difficult, and I'll have to look into it.

Public Sub ExportXLS()
#If Not CC_Debug Then
  On Error GoTo ErrProc
#Else
  On Error GoTo ExitProc
#End If
 
Const conBASE_PATH As String = "C:\Exports\"
Const conEXPORT_OBJ  As String = "qryExportMetrics"
 
'If the Folder C:\Exports does not exist, creatwe it
If Dir$(conBASE_PATH, vbDirectory) = "" Then
 MkDir "C:\Exports"
End If
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, conEXPORT_OBJ, _
                            conBASE_PATH & conEXPORT_OBJ & ".xls", True
 
ExitProc:
  Exit Sub
 
ErrProc:
  MsgBox Err.Description, Err.Source
    Resume ExitProc
End Sub

[michelle copper]

Thanks ADezii! I hope you come up with a solution soon for exporting pivot view as well.

[NeoPa]

Hijack question moved to its own thread - How to Export into Multiple Worksheets in Same Workbook.

[ADezii]

As I see it, Michele, the easiest solution by far would be to:

1. Allow All Menus in the StartUp Properties.
2. Open frmExportMetrics in Pivot Table View.
3. Click on the Pivot Table Menu Option, then Export to Microsoft Excel (last option on Menu.
4. Make any adjustments in Excel.
5. Save the Worksheet.

[michelle copper]

Alright. I'll try that instead.

Thank you so much for all responses.