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 'qryExportMetri cs'

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

** Edit **
[imgnothumb]http://bytes.com/attachments/attachment/5342d1314126028/qryexportdata.j pg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5343d1314126028/qryexportpivot. jpg[/imgnothumb]

[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 **
[imgnothumb]http://bytes.com/attachments/attachment/5346d1314192167/error.jpg[/imgnothumb]

[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 qryExportMetric s, 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