Output Multiple Pivots to Excel

[pkelly]

I have a sheet with 2 different pivot tables on it...

Pivot 1

Pivot 2

I want to export both to excel but keep them on the same excel file / sheet.

I know I can export a single table to excel using the following code...

Set obj = ActiveDocument.GetSheetObject("CH400")

obj.SendToExcel

Anyone know how I can send both at the same time?

Thanks

Paul

[#2]

Hi,

Search in this forum, This type of question has been repeatedly posted in this forum.

I remember , i have answered couple of question like this in this forum. Unfortunately i dont have any sample application as of now.

Try searching the forum.

Cheers

- Sridhar

[pkelly]

Thanks Sridhar , first thing I did was search the forum but have had no success.

I have just found the following using the API Guide ...

*** Code Starts

Set obj = ActiveDocument.GetSheetObject("CH400")

obj.ExportEx "c:\paul.xls" , 5

Set obj = ActiveDocument.GetSheetObject("CH401")

obj.ExportEx "c:\paul.xls" , 5 , true

*** Code Ends

This creates the spreadsheet but I cannot get it to append - the true statement should append...

[pkelly · Accepted solution]

Hi Sridhar

Went back and did my search again and found one of the solutions you spoke about.

Used the following...

sub ExcelOutput

' Open Microsoft Excel and Add Worksheet

Set XLapp = CreateObject("Excel.Application")

XLapp.Visible = True

Set XLdoc = XLapp.Workbooks.Add

Set XLsheet = XLdoc.Worksheets(1)

' Copy Table 1

XLsheet.Range("A3").Select

Set obj = ActiveDocument.GetSheetObject("CH400")

obj.CopyTableToClipboard True

XLsheet.Paste

' Copy Table 2

XLsheet.Range("A15").Select

Set obj = ActiveDocument.GetSheetObject("CH401")

obj.CopyTableToClipboard True

XLsheet.Paste

' Rezise Columns

XLsheet.Cells.EntireColumn.AutoFit

XLsheet.Cells.EntireRow.AutoFit

End Sub

Thanks for your help

[#5]

Hi,

k nice, but i sat yesterday and prepared one solution for you.

find the attachment.

- Sridhar

[#6]

Hi Sridhar,

Good Work.

The Application was very helpful to incorporate Different Summary reports under one Excel Sheet.

Cheers,

Mandar

[eiconsulting]

I try yr sample app and it gives me an error of "Active.X cannot create the object Excel.Application"

Active.X (?)

I am still working with version 9 but it's QlikView for Windows and not the IE app. I've also installed the OCX ...

Any idea what can be wrong?

Flavio

[suniljain]

Are you install MS Office on qlikview server ?.

[eiconsulting]

Michael Solomovich replied on Sun, Oct 24 2010 8:38 AM

Verified by djpdon123

***** ****** You have to allow system access to macro.

[#10]

Hi Sridhar,

                 Great example.I have kind of similar requirement.May be you can suggest me something.I want to export and save the object data as excel/text file based on selction made in another object.User makes aselection in a field then the object is exports as excel/text file and if user makes another selection again th esame object has to exported to same excel which was created before.Like incrementing the data one object based on different selections to same excel sheet.

I am using the following VB script but it just replaces previously created file.I want to add the data to same excel/text file based on multiple selections by the user.

sub export
set tb2 = ActiveDocument.GetSheetObject("CH02")
tb2.AppendExport "C:\ProgramData\QlikTech\Documents\export.xls" , ";"
End sub

Thank you in advance