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
18 9698
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
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.
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
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 **
'Check and see if have have a similar type Statement somewhere in your code:
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 32,556
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.
Thanks NeoPa! That was good info!
Adezii,
I tried those codes. It seems like there is an error with my pivot tables. I have attached an image again.
** Edit **
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?
sure! I will PM you.
Thanks
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.
- Sorry Michele, I do not have Access 2003. Can you Convert the DB to this Version (20030 and Re-Attach?
- Are you trying to Export the Pivot Tables View, or the Pivot Tables themselves?
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
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
Thanks ADezii! I hope you come up with a solution soon for exporting pivot view as well.
NeoPa 32,556
Expert Mod 16PB
As I see it, Michele, the easiest solution by far would be to: - Allow All Menus in the StartUp Properties.
- Open frmExportMetrics in Pivot Table View.
- Click on the Pivot Table Menu Option, then Export to Microsoft Excel (last option on Menu.
- Make any adjustments in Excel.
- Save the Worksheet.
Alright. I'll try that instead.
Thank you so much for all responses.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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
|
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...
|
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".....
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
|
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...
|
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...
|
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,...
|
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...
| |