Pivot table only exports one column from macro

Thu, 28 Feb 2013 21:54:47 GMT

I have a pivot table which normally shows anywhere from 5 to 30 columns, depending on how many calls a rep has made that day (1 column per call).

I am trying to create a macro to do this automatically, but when I run the macro, only the first column shows up. If I click on the "Export to Excel" icon on the chart in QV, it exports to Excel just fine. Here's my macro code:

Sub MakeExcelFile
' This sub opens an Excel template, copies the chart over, reformats the Excel
' file using a macro, and then saves the file to a unique file name.

' First, copy the chart

ActiveDocument.GetSheetObject("CH03").CopyTableToClipboard TRUE

'Create the save file name

set s1 = ActiveDocument.Variables("vCurRep")
set s2 = ActiveDocument.Variables("vCallDate")
f1 = s1.GetContent.String
f2 = s2.GetContent.String
Path = "S:\ADMIN\USERS\KBER\Boomerang\"

' Next, open Excel and then open the template file

set XLAPP=CreateObject("Excel.Application")

XLApp.Visible = FALSE

set XLDOC = XLApp.Workbooks.Open("S:\ADMIN\USERS\KBER\Boomerang\DCSTemplate.xlsm", 0, True)

' Paste the chart on Sheet1

XLDOC.Sheets(1).Paste()

' Run the macros

XLApp.Run "DailyCallSummaryFormat"
XLApp.Run "CreateThePrintArea"

' Save the file

XLDOC.SaveAs Path & f1 & f2
XLDOC.Close
End Sub

Does anyone have any ideas why the entire table is not being copied?

Thanks

topic Pivot table only exports one column from macro in QlikView

Pivot table only exports one column from macro