QV to Formated Excel file

[#1]

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