469,339 Members | 8,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,339 developers. It's quick & easy.

Exporting Access Pivot tables to Excel

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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportXLS()
  2.  
  3. #If Not CC_Debug Then
  4. On Error GoTo ErrProc
  5. #End If
  6.  
  7.     Const cQuery As String = "qryExportMetrics"
  8.  
  9.  
  10.     Dim fc As FileChooser
  11.     Dim strFileName As String
  12.  
  13.     Set fc = New FileChooser
  14.     fc.DialogTitle = "Select file to save"
  15.     fc.OpenTitle = "Save"
  16.     fc.Filter = "Excel Files (*.xls)"
  17.     strFileName = Nz(fc.SaveFile, "")
  18.     Set fc = Nothing
  19.  
  20.     ' If user selected nothing or canceled, quit
  21.     If Len(strFileName) = 0 Then
  22.         Exit Sub
  23.     ' If file already exists, delete it
  24.     ElseIf Len(Dir(strFileName)) > 0 Then
  25.         Kill strFileName
  26.     End If
  27.  
  28.     DoCmd.TransferSpreadsheet _
  29.         acExport, _
  30.         acSpreadsheetTypeExcel9, _
  31.         cQuery, _
  32.         strFileName, _
  33.         HasFieldNames:=True
  34.  
  35.  
  36.  
  37.  
  38. ExitProc:
  39.     Exit Sub
  40. ErrProc:
  41.     ErrMsg Err, Err.Description, Err.Source
  42.     Resume ExitProc
  43.  
  44.     End Sub
Aug 22 '11 #1
18 9279
ADezii
8,800 Expert 8TB
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?
Expand|Select|Wrap|Line Numbers
  1. Dim astrPivotTables As Variant
  2. Dim bytTblCtr As Byte
  3. Const conBASE_PATH As String = "C:\Exports\"
  4.  
  5. 'Populate an Array with ALL the Pivot Table Names
  6. astrPivotTables = Array("Customers", "Orders", "Order Details")
  7.  
  8. 'Export ALL Pivot Tables to their individual Spreadsheets
  9. For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  10.   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
  11.                             conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
  12. Next
Aug 22 '11 #2
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?


Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportXLS()
  2.  
  3. #If Not CC_Debug Then
  4. On Error GoTo ErrProc
  5. #End If
  6.  
  7. Dim astrPivotTables As Variant
  8. Dim bytTblCtr As Byte
  9. Const qryExportMetrics As String =&nbs
  10. p;"C:\Exports\"
  11.  
  12.  
  13. 'Populate an Array with ALL t
  14. he Pivot Table Names
  15.  
  16. astrPivotTables = Array("MetricsID", "Activity", "Months")
  17.  
  18.  
  19. 'Export ALL Pivot Tables to t
  20. heir individual Spreadsheets
  21.  
  22. For bytTblCtr = LBound(astrPivotTables)
  23.  To UBound(astrPivotTables)
  24.  
  25.   DoCmd.TransferSpreadsheet acExport,&n
  26. bsp;acSpreadsheetTypeExcel9, astrPivotTables(bytTb
  27. lCtr), _
  28.  
  29.  
  30.          &
  31. nbsp;        &n
  32. bsp;conBASE_PATH & astrPivotTables(bytTbl
  33. Ctr) & ".xls", True
  34.  
  35.  
  36.  
  37.  
  38. ExitProc:
  39.     Exit Sub
  40. ErrProc:
  41.     ErrMsg Err, Err.Description, Err.Source
  42.     Resume ExitProc
  43.  
  44.     End Sub
  45.  

and yes you are right... I wanted to export the data of the query in one sheet and the pivot tables in other spreadsheet.
Aug 23 '11 #3
ADezii
8,800 Expert 8TB
Michele, I do not fully understand the Syntax in Code Lines 9, 10, 31 and 32. Kindly explain.
Expand|Select|Wrap|Line Numbers
  1. Line # 9: Const qryExportMetrics As String =&nbs 
  2. Line #10: p;"C:\Exports\"
  3. Line #31: nbsp;        &n 
  4. Line #32: bsp;conBASE_PATH & astrPivotTables(bytTbl 
Aug 23 '11 #4
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportXLS()
  2.  
  3. #If Not CC_Debug Then
  4. On Error GoTo ErrProc
  5. #End If
  6.  
  7. Dim astrPivotTables As Variant
  8. Dim bytTblCtr As Byte
  9.  
  10. Const qryExportMetrics As String = "C:\Exports\"
  11.  
  12. 'Populate an Array with ALL the Pivot Table Names
  13. astrPivotTables = Array("Activity", "Visit Date")
  14.  
  15. 'Export ALL Pivot Tables to their individual Spreadsheets
  16. For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  17.   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
  18.                             conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
  19. Next
  20.  
  21. ExitProc:
  22.     Exit Sub
  23. ErrProc:
  24.     ErrMsg Err, Err.Description, Err.Source
  25.     Resume ExitProc
  26.  
  27. 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 **


Attached Images
File Type: jpg qryExportData.jpg (54.4 KB, 3966 views)
File Type: jpg qryExportPivot.jpg (43.3 KB, 3983 views)
Aug 23 '11 #5
ADezii
8,800 Expert 8TB
'Check and see if have have a similar type Statement somewhere in your code:
Expand|Select|Wrap|Line Numbers
  1. #Const CC_Debug = True
then try the following and see if it works:
Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportXLS()
  2. #If Not CC_Debug Then
  3.   On Error GoTo ErrProc
  4. #Else
  5.   On Error GoTo ExitProc
  6. #End If
  7.  
  8. Dim astrPivotTables As Variant
  9. Dim bytTblCtr As Byte
  10.  
  11. Const conBASE_PATH As String = "C:\Exports\"
  12.  
  13. 'Populate an Array with ALL the Pivot Table Names
  14. astrPivotTables = Array("Activity", "Visit Date")
  15.  
  16. 'Export ALL Pivot Tables to their individual Spreadsheets
  17. For bytTblCtr = LBound(astrPivotTables) To UBound(astrPivotTables)
  18.   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, astrPivotTables(bytTblCtr), _
  19.                             conBASE_PATH & astrPivotTables(bytTblCtr) & ".xls", True
  20. Next
  21.  
  22. ExitProc:
  23.   Exit Sub
  24.  
  25. ErrProc:
  26.   MsgBox Err.Description, Err.Source
  27.     Resume ExitProc
  28. End Sub
Aug 23 '11 #6
NeoPa
32,182 Expert Mod 16PB
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.
Aug 24 '11 #7
Thanks NeoPa! That was good info!
Aug 24 '11 #8
Adezii,

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

** Edit **

Attached Images
File Type: jpg Error.jpg (41.8 KB, 3989 views)
Aug 24 '11 #9
ADezii
8,800 Expert 8TB
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?
Aug 24 '11 #10
sure! I will PM you.
Thanks
Aug 24 '11 #11
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.
Attached Files
File Type: zip working_in_progress.zip (106.2 KB, 138 views)
Aug 24 '11 #12
ADezii
8,800 Expert 8TB
  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?
Aug 24 '11 #13
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
Attached Files
File Type: zip working_in_progress_for 2002-2003.zip (106.0 KB, 136 views)
File Type: zip working_in_progress_for 2000.zip (177.8 KB, 152 views)
Aug 24 '11 #14
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportXLS()
  2. #If Not CC_Debug Then
  3.   On Error GoTo ErrProc
  4. #Else
  5.   On Error GoTo ExitProc
  6. #End If
  7.  
  8. Const conBASE_PATH As String = "C:\Exports\"
  9. Const conEXPORT_OBJ  As String = "qryExportMetrics"
  10.  
  11. 'If the Folder C:\Exports does not exist, creatwe it
  12. If Dir$(conBASE_PATH, vbDirectory) = "" Then
  13.  MkDir "C:\Exports"
  14. End If
  15.  
  16. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, conEXPORT_OBJ, _
  17.                             conBASE_PATH & conEXPORT_OBJ & ".xls", True
  18.  
  19. ExitProc:
  20.   Exit Sub
  21.  
  22. ErrProc:
  23.   MsgBox Err.Description, Err.Source
  24.     Resume ExitProc
  25. End Sub
Aug 24 '11 #15
Thanks ADezii! I hope you come up with a solution soon for exporting pivot view as well.
Aug 25 '11 #16
NeoPa
32,182 Expert Mod 16PB
Hijack question moved to its own thread - How to Export into Multiple Worksheets in Same Workbook.
Aug 25 '11 #17
ADezii
8,800 Expert 8TB
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.
Aug 25 '11 #18
Alright. I'll try that instead.

Thank you so much for all responses.
Aug 25 '11 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.