Hello Everybody,
I am trying to export the pivot table with current selection to excel, I am facing the following problems.
1. The pivot table format is changed in Excel like a flat data, how do I get the pivot table as like in QV.
2. Where do I use the formating the object in the following code.
Sub to_excel
Dim wsname
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = false
Set XLDoc = XLApp.Workbooks.Add
wsname = ""
wsname = "Sheet1"
strFileName = "C:\test\Example1.xls"
ActiveDocument.ClearAll True
set obj = ActiveDocument.GetSheetObject("CH27")
obj.CopyTableToClipboard true
XLDoc.Sheets(wsname).Range("A7").Select
XLDoc.Sheets(wsname).Paste
XLApp.DisplayAlerts=false
XLDoc.SaveAs strFilename
XLDoc.Close
XLApp.Quit
end sub
Regards
Joe