topic Output Multiple Pivots to Excel in QlikView

Output Multiple Pivots to Excel

pkelly — Wed, 27 May 2009 18:08:15 GMT

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

Output Multiple Pivots to Excel

Wed, 27 May 2009 19:43:15 GMT

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

Output Multiple Pivots to Excel

pkelly — Wed, 27 May 2009 19:52:37 GMT

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...

Output Multiple Pivots to Excel

pkelly — Wed, 27 May 2009 21:02:39 GMT

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

Output Multiple Pivots to Excel

Thu, 28 May 2009 11:31:43 GMT

Hi,

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

find the attachment.

- Sridhar

Output Multiple Pivots to Excel

Tue, 25 Jan 2011 07:03:39 GMT

Hi Sridhar,

Good Work.

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

Cheers,

Mandar

Output Multiple Pivots to Excel

eiconsulting — Mon, 07 Mar 2011 10:08:22 GMT

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

Output Multiple Pivots to Excel

suniljain — Mon, 07 Mar 2011 10:27:37 GMT

Are you install MS Office on qlikview server ?.

Output Multiple Pivots to Excel

eiconsulting — Mon, 07 Mar 2011 11:00:30 GMT

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

Verified by djpdon123

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

Re: Output Multiple Pivots to Excel

Thu, 29 Mar 2012 19:25:20 GMT

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