Automate Pivot Export To Excel

Aug 8, 2006

Hi all, I'm using access 2002. 2002 has pivoting, but in order to use it you have to download an xp add on(at least I did). After I installed the libraries pivots worked for me- and I added all manner of niftyness-before I discovered that no one else in my department could use it without adding the same downloads. So I decided to go with exporting to excel. That opened up a whole new can of worms, but in the end I finally got it working. I just wanted to share what I found with everyone here-maybe the next person won't have to work so hard. This code works from a toolbar button.
Of course if anyone has any suggestions, I'm interested.

Public Function goToPivot()
'automates creating a formatted pivotChart in excel from a query in access2002
'because of some trickiness with objects, no 'with's are used
On Error GoTo Err_goToPivot

Dim xlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim ExcelFile As String
Dim queryPivot As String

'set relative path and filename of new spreadsheet
ExcelFile = Application.CurrentProject.Path & "xPivot.xls"
queryPivot = "querypivotChartTest"

' Delete file if it exists
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ExcelFile) Then
' Delete if not read only
fso.DeleteFile ExcelFile, False
End If

'export query to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
queryPivot, ExcelFile, True

Set xlApp = New Excel.Application
xlApp.Visible = True
Set XlBook = xlApp.Workbooks.Open(ExcelFile)

'set style and range of cells, name pivotTable
DataRange = queryPivot & "!" & XlBook.Worksheets(queryPivot).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = XlBook.PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="Pivot_Table1", _
DefaultVersion:=xlPivotTableVersion10)

'create pivotChart, preload it with fields to get user started
XlBook.Charts.Add
XlBook.ActiveChart.Location xlLocationAsNewSheet, "RCA pivot"
XlBook.ActiveChart.PivotLayout.PivotTable.AddDataF ield XlBook.ActiveChart.PivotLayout. _
PivotTable.PivotFields("SIRs"), "Count of SIRs", xlCount
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Orientation = xlRowField
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Position = 1


'set axes and chart titles, size and fonts of pivotChart
XlBook.ActiveChart.HasTitle = True
XlBook.ActiveChart.ChartTitle.Characters.Text = "RCA DATA ANALYSIS"
XlBook.ActiveChart.ChartTitle.Font.Bold = True
XlBook.ActiveChart.ChartTitle.Font.Size = 18
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "CATEGORY"
XlBook.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "TOTAL"
XlBook.ActiveChart.SizeWithWindow = True



'optional-create, save, then close
'XlBook.Saved = True
'xlApp.Quit
'Set XlPT = Nothing
'Set XlBook = Nothing
'Set xlApp = Nothing


Exit_goToPivot:
Exit Function

Err_goToPivot:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_goToPivot

End Function

View Replies


ADVERTISEMENT

Export Access Pivot Table To Excel

Jul 30, 2006

Hi,

Does anyone know how to export a pivot table to Ms Excel without using the specific button in the Pivot Table View of the form?

Thanks for the help:confused:

View 5 Replies View Related

Export Pivot Table View To Excel

Jan 25, 2012

so I have this code for exporting all my other queries

Code:
Private Sub Command4_Click()
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryGEM"

[code]...

The codes work perfectly fine but it doesnt work well for pivot table view. What should I add to the add to make it work? I dont want pivot table in datasheet. I did try the codes posted by other people but it works well for if I have only ONE QUERY to export. As you can see, the above codes are for exporting more than one query.

View 3 Replies View Related

Export Pivot Table Query View In Excel

Jun 26, 2007

Hello,

I need to export a Pivot Table view to Excel. The issue is with Calculated Total columns which are not exported in excel. Do you know if there is any way I can export the Calculated columns in excel?

Thank you.

View 2 Replies View Related

General :: Export Pivot Table To Excel But Retain Values

Jan 25, 2015

I am trying to export my Access pivot table to excel because every time I do, the values (which are text and numbers; i.e. some numbers and some <0.05 format) are changed into a sum, product..... I need to rearrange this data not calculate anything.

View 14 Replies View Related

Automate The "Export To Excel" Function?

May 10, 2007

Hi guys,

I regularly export the main table in our database to an excel spreadsheet, to provide an additional level of data back-up. (Using File/Export)

I was wondering if there is a way of automating this process, either through a command button on a form, or by linking it with shutting the database down.

Any ideas?

View 1 Replies View Related

Automate XML Export

Jul 11, 2006

Hi:
I want to automate the XML export function of Access and not sure if it is possible or how to do it. In going through the menu bar (File ->Export) interface, I must choose the root table, and then subsequent tabbed windows allow one to select what tables to be exported, if I want the schema to be generated, the target filename, etc.
Instead of having to go through this large selection process, I'd like to write a script that I can run consistently over the same tables, produce the same file name, etc. Can anybody provide a suggestion/code/pointer on how to accomplish this? Thanks in advance.
John

View 1 Replies View Related

Automate An Export Of Data To Access Tables.

Jul 28, 2006

Hi,

Would it be possible for me to build an interface for a customer to use for importing data. So that they customer could choose from a drop down list, or input into a text box where they want to export from, and where they would like the export to be imported?

View 1 Replies View Related

Queries :: Automate Outlook Task To Export Common Query Every Day

Jun 29, 2013

I've created an outlook task to export a common query I do every day, is there a way to automate it so it will happen automatically without me pressing Run?

I use office 2007 on a company laptop with tight network restrictions (so no Windows Task Scheduler)..

View 1 Replies View Related

Forms :: Export PIVOT Table Filtered Data

May 13, 2014

I have a PIVOT table that is used to select courses. I have some vba code that exports this to pdf, but it exports the entire data not the filtered data. Is this possible just to export the filtered data?

View 8 Replies View Related

General :: MS Access To Automate With Excel

Mar 6, 2013

I have encountered PC Lockdown when I tried this onClick Code. I am trying to post a data from MSAccess field into a specific cell in Excel. The Excel File is formatted already as document. It is submitted for Attendance records. But I couldn't find the problem and fix to automate it properly.

Code:
Private Sub cmdRequery_Click()
On Error GoTo Err_AttSum
Dim xlApp As Excel.Application

[code]...

View 9 Replies View Related

Access Query With Links To Excel / Export To XML And Back Into Excel

Apr 25, 2013

I am using Excel and Access 2010.

I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.

My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.

My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.

My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?

View 7 Replies View Related

Modules & VBA :: Export To Excel And Call Macro From Other Excel?

Aug 25, 2013

i want to export a table to excel , open this file and execute a macro from another file.

the code i have now is :

Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"

Opening the excel file goes ok, running the macro however not.

View 1 Replies View Related

Queries :: Copy Data To Excel From Pivot Table

May 21, 2015

I have the following (simplified) normalised data table:

[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200
...

I use the query below to pivot the data into the right format for export:

Code:
TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual"
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account

which yields the following table as the query result:

SrcUD1 col1 col2 col3 col4 col5 col6 col7
row1
row2
row3
row4
...

which is great except that I want to transfer the results to Excel using the CopyFormRecordset method without the metadata of column 1 (row1, row2 etc.

View 1 Replies View Related

Modules & VBA :: Creating Excel Pivot Chart In Access

Jan 21, 2014

I've written this code in Access to create a pivot chart in Excel. All the code listed is good except for the last line. The last line creates another instance of Excel. I can't figure out what the correct syntax is.

Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open("c:chi estpivot.xlsx"
wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

[code]....

View 14 Replies View Related

Filtering And Exporting Data To Excel For Pivot Tables

Jul 15, 2015

I have an Access database with millions of records.I am only interested in a subset of records (250,000+) that I would like to analyze in an Excel pivot-table. My issue is; how do I best export this information to Excel.I've tried filtering and exporting, but all records are exported. I've tried copy and paste but only 65K are allowed. Is there a better way to do this, perhaps linking the Access database?

View 1 Replies View Related

Rename Access File With Excel Pivot Table Link

Jan 8, 2007

Hi,
I would like to rename my access file. My problem: I have many pivot in excel link to this database so if i rename it all the links will be down...How can i resolve this?:confused: (of course rebuild all pivots could be a solution but I have around 50 pivots behind my database)
Thanks for your help!

View 1 Replies View Related

General :: Cannot Publish Excel Pivot Chart - Run Time Error

Oct 11, 2013

I am trying to publish an Excel Pivot Chart, but keep getting Run Time Error 1004 - Application-defined or object-defined error...

I have tried the code 2 ways:

Code:
Sub PublishChart() Worksheets("Historical_Pivot").Activate
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", _
"Historical_Pivot", "JAMALCO", xlHtmlStatic, "ABC REQ BACKLOG_2013", "")
.Publish (True)
.AutoRepublish = False
End With
End Sub

Code:
Sub PublishChart()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbookSet ws = wb.Sheets("Historical_Pivot")
ws.ChartObjects("JAMALCO").Activate
With wb.PublishObjects.Add(xlSourceChart, Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", ws _
, "", xlHtmlStatic, "", "")
.Publish (True)
End With
End Sub

View 1 Replies View Related

Tables :: DateTime - Running Pivot Queries To Excel To Do Analysis Of Data

Aug 27, 2013

I have a larget transaction data set in access with Datetime column/filed.

I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.

What is the best way to reduce the datatime field to date only and where should this be done?

i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?

View 7 Replies View Related

General :: Access Data Export Into Excel As Data Linked To Excel

Oct 21, 2012

how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;

DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"

Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.

View 5 Replies View Related

Help With Export To Excel

Nov 4, 2005

I have a table of applications (200 applications), with the following columns for each application:

AppName
Description
BusCrit
BU
NumUsers

I need to export this to an excel spreadsheet into specific rows and columns and then save the spreadsheet, with it named for the application (ie, App1.xls) which it should get from the AppName column in the table. How would I do this?

View 1 Replies View Related

Export / Import To And From Excel

May 5, 2005

hi,

I'm having a small but annoying problem exporting data to excel and then importing it back..

The problem is that when i export a table to Excel - it changes the date format from dd/mm/yy (which is what i have in Access) to DD-MMM-YY (automatically in Excel)

This proves to be a problem because when i import the spreadsheet back Access gives me a data type conversion error.

Its easily solved manually by changing the field in excel to dd/mm/yy format but annoying as the user will have to do this everytime they export and import!

Any suggestions??

Thanks in Advance..

View 6 Replies View Related

How To Export A Report To Excel

Oct 17, 2005

I've been asked to export an MS Access report to Excel. I'm having trouble with including subtotals, totals, etc. in the Excel sheet. I've enclosed a picture of how my report looks in Access, and was wondering if there's a way to export everything the report has into Excel......

Can anyone maybe point me in a good direction, or to a link that has info re: what I want to do?

And before anyone asks, it's for a manager who insists that everything be done in Excel, since that is what he's comfy with. Outside of that, I wouldn't be giving myself headaches, since my Access report contains everything needed.

Thanks in advance!

View 6 Replies View Related

Export To Excel And Graph

Jan 4, 2006

I am trying to export the result of a query to a specific Excel sheet where I have a dynamic graph. The results are exported on a monthly basis, so I am just adding the data, and the graph reflects that. Now, I can export to excel easily enough by using the Transfer Spreadsheet method, but this creates a new worksheet, thus destroying my lovely graph. I have even tried the Output Query method, but to no avail. Is there any way I can export to excel and keep the graph intact?

David

View 2 Replies View Related

Help Modifying Export To Excel.

Sep 1, 2007

Hi guys, me again. Have a few functions that I would like to put into action.
1)
I currently have the following code that exports multiple queries to excel. All works great I am happy to say. Currently the date criteria are hard coded into the queries. Well now my users would like to enter date ranges instead. Currently when I click on my toggle button, the users are asked if they are sure they want to start export to excel. I still want that to happen, but how can I change my code to prompt for startdate and enddate before running the export. I would rather use an input boxes to enter the date range instead of using a form. Would this be possible?

2)
Right now excel is displayed on the screen while the export is in process, how can I hide excel and then have it appear after its done with export. If the user clicks within excel during export, it sometimes freezes. And how can I apply my module for the progress bar to display the progress of the export. I have provided the code of the progress bar that I am using, that I found on this forum.

Many thanks for any suggestions.



Option Compare Database
Option Explicit

Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String


If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If

'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "BigLarOutput.xls"
strFileTemplate = "BigLarTemplate.xls"
strMacroName = "DeleteBlank"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName


'This is a custom function I built to set Excel as an object and you can access/export
'to a workbook programmatically. 'openexcel' is stored in a module called Functions.
'This will open the new file that was created previously

openexcel strFilePath & strFileName

'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Export data is another function that will export your data.
'Update the query Names to your real Query Names
'Update the Sheet Names accordingly, with the Query it is assiged to
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
ExportData "qryHoeqDotDenied", "HOEQ DOT DENIED"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''


xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save

'Uncomment/Comment these to close out the workbook
'xl.ActiveWorkbook.Close
'xl.Quit

Set xl = Nothing

End Function


Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim rs As Recordset

'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.

'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record

'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select

'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'

'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook

'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.

xl.cells(intR + 3, 1).Value = rs.Fields(0)
xl.cells(intR + 3, 2).Value = rs.Fields(1)
xl.cells(intR + 3, 3).Value = rs.Fields(2)
xl.cells(intR + 3, 4).Value = rs.Fields(3)
xl.cells(intR + 3, 5).Value = rs.Fields(4)
xl.cells(intR + 3, 6).Value = rs.Fields(5)


'If there are more columns to add than this just follow what I have above

'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row

'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select

'Clears the recordset
rs.Close
Set rs = Nothing

End If

End Function

' Code inside progressbar module

Option Compare Database
Option Explicit

Function RunProgressBar(lLth As Long)

If IsLoaded("frmSplash") Then
Forms!frmSplash!Box20.Width = lLth
End If

End Function

Function IsLoaded(ByVal strFormName As String) As Boolean
On Error GoTo Err_IsLoaded

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

Exit_IsLoaded:
Exit Function

Err_IsLoaded:
MsgBox Err.Description, , " Service Operations"
Resume Exit_IsLoaded

End Function

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

Dim PauseTime As Variant, Start As Variant

PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

Exit_Pause:
Exit Function

Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause

End Function

View 7 Replies View Related

Not Being Able To Export Excel To Access

Nov 23, 2007

What can be the reason ?
i'm doing it through a regular "Import Spreadsheet Wizard", and get a message "an error occured trying to import file"
File is not open. Made a copy under a different name; still can't import..

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved