473,836 Members | 1,510 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
18 9731
michelle copper
63 New Member
sure! I will PM you.
Thanks
Aug 24 '11 #11
michelle copper
63 New Member
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, 143 views)
Aug 24 '11 #12
ADezii
8,834 Recognized Expert Expert
  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
michelle copper
63 New Member
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, 154 views)
File Type: zip working_in_progress_for 2000.zip (177.8 KB, 161 views)
Aug 24 '11 #14
ADezii
8,834 Recognized Expert Expert
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.
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
michelle copper
63 New Member
Thanks ADezii! I hope you come up with a solution soon for exporting pivot view as well.
Aug 25 '11 #16
NeoPa
32,584 Recognized Expert Moderator MVP
Hijack question moved to its own thread - How to Export into Multiple Worksheets in Same Workbook.
Aug 25 '11 #17
ADezii
8,834 Recognized Expert Expert
As I see it, Michele, the easiest solution by far would be to:
  1. Allow All Menus in the StartUp Properties.
  2. Open frmExportMetric s 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
michelle copper
63 New Member
Alright. I'll try that instead.

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

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

Similar topics

1
4138
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
1959
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
8251
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
4205
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
3258
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
2638
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
2083
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
3508
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
9666
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
10838
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10544
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
10585
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,...
1
7788
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
6977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
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...
0
5821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.