Unable to make a pivot table in excel sheet2

[Sinner]

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

 

[Steve Schapel]

Sinner,

The focus of this newsgroup is macros in Access, which is not related to VBA
as you are using.

As this relates to Office automation process, you will have a better chance
of a good answer if you re-post your question to the External Data
newsgroup.

 

[Angela]

Thank you.
Posted at "microsoft.public.access.externaldata".