473,899 Members | 3,826 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting Access Pivot tables to Excel

63 New Member
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 9737
ADezii
8,834 Recognized Expert Expert
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
michelle copper
63 New Member
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,834 Recognized Expert Expert
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
michelle copper
63 New Member
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 'qryExportMetri cs'

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

** Edit **


Attached Images
File Type: jpg qryExportData.jpg (54.4 KB, 4093 views)
File Type: jpg qryExportPivot.jpg (43.3 KB, 4123 views)
Aug 23 '11 #5
ADezii
8,834 Recognized Expert Expert
'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,584 Recognized Expert Moderator MVP
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
michelle copper
63 New Member
Thanks NeoPa! That was good info!
Aug 24 '11 #8
michelle copper
63 New Member
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, 4110 views)
Aug 24 '11 #9
ADezii
8,834 Recognized Expert Expert
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

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

Similar topics

1
4142
by: Johnny Meredith | last post by:
Dear All, I have an Access database that tracks the progress of income tax audits. When the taxing authorities make a change (an "Adjustment"), I record the pertinent information in the database. At the end of the audit cycle, these adjustments are used to compute the revised taxes due. This computation is done in a fairly complex, but accurate and easy to use, spreadsheet. In the spreadsheet, there is a sheet that lists out all...
1
1961
by: michb | last post by:
I need to export data from access to an excel database to calculate times and wages for the week but the code that I entered doesn't seem to like either me or the database! I need it to go to one specific sheet in an excel workbook - the code used is following as was advised earlier - but I am having no luck and I want to get this database working asap. code is: Option Compare Database Public Function TransferSpreadsheet() Sub...
1
1460
by: sanniep | last post by:
Dear Expert, I would like to program a button to export an Access pivotchart to Excel. The predefined button works ok but I would like to automate it. Thanks, Sander
6
10128
by: Hoelper | last post by:
When creating a Pivot Table in MS Access, why does the table lose significant digits (e.g., 4.45) and only report rounded whole numbers (e.g., 4)? Can this be fixed? FYI, the database table is already set to decimal.
6
8262
by: Swapnil Vaidya | last post by:
Hi All, I have got stucked up in one problem from many days. I have one report in Access 97. When I do right click on it and select option "Save As./Export". It gives me Error as "Overflow".. Actually I am very new to Access. So please help me.
12
4207
by: TARHEELS721 | last post by:
I am trying to send the results of a query that runs when I click a button on my form that is based on a parameter query. The code runs without any errors but nothing is exported into my excel spreadsheet and I can't figure out why does anyone see where I went wrong in my code. I am currently using DAO Recordset. Here is what my code looks like: Private Sub btnJE_Click() 'Exports qryJE results into excel On Error GoTo Err_btnJE_Click ...
3
3260
by: hawaiijeff | last post by:
I have a report that I built in Access 2002. I built it with the report wizard, but then went into design mode and added a extra information which was calculations off the existing fields in the report (these calculated values are not pulled in the original query). So the report looks great and performs as expected, but when I export it to Excel I lose all of the extra fields that I added (and of course all of the formatting). All that it...
1
2639
benchpolo
by: benchpolo | last post by:
I have data extracted from Access db to Excel with a pivot table. Somehow, I am having issues with the pivot table were it doesnt update the totals. For example, the first extract i did in Access has 6000 data rows and pivot table in excel should refresh on open and reflect the 6000 data rows. 2nd extract in Access has 10000 data rows when pivot table opens it doesn't refresh the summary count on the pivot table based on the 10000 data...
2
2085
by: Comandur | last post by:
Hi, I am trying to export an access query to excel. I have made use of transferspreadsheet command to achive this. However i have hardcoded the path and the filename in the VBA code. I am not sure as to how i can make the user enter his ownfile name and select the location where the file is to be written. I am attaching below my code: Private Sub Report_Click() On Error GoTo Err_Report_Click Dim db As DAO.Database
3
3510
by: sarah2855 | last post by:
Hello All, I'm looking for the vb code that export an access table to specific worksheet in Excel. I tried to search see if this question was answered before here, but didn't find anything that really relates to this question. I will appreciate your help, thanks in advance.
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10971
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8039
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.