Unable to make a pivot table in excel sheet2

S

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
 
S

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.
 

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

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
make exported excel field uppercase 2
Pivot table code question 2
Why R1C1 range parameters in VBA Pivot Table setup/ 18
VBA to create Pivot table from text file 2
Pivot table in VB 3

Top