Unable to make a pivot table in sheet2

A

Angela

Hi,

Can someone pls check below code.
I'm able to export a query from access to excel in file "Myfile.xls"
sheet1 but unable to make a sheet2 with pivot table of sheet1 data.

Option Compare Database
Public Sub TransferReport()

Dim varFileName As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim MyRange As String

varFileName = "D:\MyFile.xls"
'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MONTH
END REPORT", varFileName, False, "Sheet1"

Set xlWb = ActiveWorkbook
xlWb.Sheets.Add
Set xlWs = xlWb.Sheets("Sheet2")


xlWs.Cells(2, 1).CopyFromRecordset rsXcl

xlWs.Range("A1").Addresslastcell = xlW.Range("A1").SpecialCells
(xlCellTypeLastCell).Address
MyRange = Range("$A$1:" & lastcell)

' Add pivot table
With xlWb
.PivotCaches.Add SourceType:=xlDatabase, _
SourceData:=MyRange
.CreatePivotTable TableDestination:="", _
tablename:="Pivottable1"
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unable to make a pivot table in excel sheet2 2
Unable to make a pivot table in excel sheet2 0
Pivot table in Excel. Exported data from access as excel sheet. 0
Export Access pivot table to Excel 0
Export Access pivot table to Excel - VBA solution 0
Problem w/ Code- Error 91 @ run-time 1
I need to export a query to Excel pivot table 3
Error in Excel VBA Code (Error 91) 1

Top