Export pivot table data to Excel (1 Viewer)

[tinyevil777]

Morning all!

Probably a very easy question, but how do i export the data in my pivot table to Excel? I understand that i can do this via menus etc, but i want this to be hardcoded into a button.

I've tried using the TransferDatabase/Spreadsheet option, but i can't seem to get it working.

Any help is greatly appreciated, thanks in advance!

Tom

 

[DCrake]

This is a routine I use copy a query that contains my dataset to an Excel Workbook wherre my pivot table is located

Public Function Export2Excel() As Boolean

On Error Resume Next
Dim sSql As String
Dim rCnt As Integer
Dim Rs As DAO.Recordset

'/Export the new dataset
sSql = "Select SiteName, Supplier, [Product Name], Category, [Pack Size], Quantity,DeliveryDate From QryPivotData"

Set Rs = CurrentDb.OpenRecordset(sSql)
    
    If Not Rs.BOF And Not Rs.EOF Then
        Rs.MoveLast
        rCnt = Rs.RecordCount
        Rs.MoveFirst
    Else
        MsgBox "Cannot find any deliveries between the dates you have chosen.", vbExclamation + vbOKOnly, "Export Abandoned"
        Set Rs = Nothing
        Export2Excel = False
        Exit Function
    End If
    
'Export the query and open Excel
   'Start a new session in Excel
    If Dir(CurrentProject.Path & "\Outlets\Deliveries.xlsx") <> "" Then
        
        'This uses late binding method
        Set xlapp = CreateObject("Excel.Application")
        Set xlbook = xlapp.Workbooks.Open(CurrentProject.Path & "\Outlets\Deliveries.xlsx")
        Set xlsheet = xlbook.Worksheets(1)
        '/Delete any previous data
        For x = 1000 To 3 Step -1
            xlsheet.range("A" & x).EntireRow.Delete
        Next
            
        xlsheet.range("A2").CopyFromRecordset Rs

         Set xlsheet = xlbook.Worksheets(2)
        '/Define the filtered date range
        xlsheet.range("B3").Value = "Deliveries between " & GetDateLower() & " and " & GetDateUpper()
            
           
            
        Rs.Close
        Set Rs = Nothing
        
        xlbook.Save
        xlapp.Quit
        Export2Excel = True
    End If

End Function

 

[tinyevil777]

Thank you David, i'll give that a whirl and get back to you with my results!

 

[tinyevil777]

Unfortunately, i couldn't get your code to work. I entered it into the OnClick of the button, but it asked me to debug it, and i'm not sure where to start debugging.

Is there a way i can invoke the Export to Excel window? (please see attached)

FYI, im using Access 2007

 

[DCrake]

What does YOUR code look like? and where did it error?

 

[Banana]

If you have a PivotTable form, you can just do this behind the form;

Me.PivotTable.Export , 1 'plExportActionOpenInExcel

 

[dee_jay]

I know it's old but, for anyone else that may look at this.
I'm Not a guru at access or VBA (or anything for that matter) but I just use a typical macro to open the report and runcommand to PivotTableExportToExcel




Maybe that will help someone, someday