topic Re: Question about exporting pivot table to excel in QlikView

Question about exporting pivot table to excel

Mon, 25 Nov 2013 10:34:05 GMT

Hi,

I need to export the pivot table to excel. But when I try to do a send to excel, the merge cell in the pivot table is split into multiple cell when I view it in excel. Anyone know how to resolve this? I try to do a copy to clipboard->full table, but all my borders line disappear or become black.

Thanks.

yongde

Re: Question about exporting pivot table to excel

Mon, 25 Nov 2013 10:35:48 GMT

Try to use macro

Re: Question about exporting pivot table to excel

Mon, 25 Nov 2013 10:44:35 GMT

Hi, Thanks for your prompt reply. Can help to provide me a sample on that?

yongde

Re: Question about exporting pivot table to excel

Mon, 25 Nov 2013 10:52:02 GMT

Try This macro......

sub test

'Set the path where the excel will be saved

filePath = "C:\Test.xls"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

'first chart object

Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH06") ' give the name of your object instead of CH06

usedRows=0

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

curSheet.Cells(usedRows+1, 1)=chartCaption

i.CopyTableToClipboard true

curSheet.Cells(usedRows+3, 1).Select

curSheet.Paste

usedRows=curSheet.UsedRange.Rows.Count+3

'loop end

excelFile.Visible = true

'Save the file and quit excel

curWorkBook.SaveAs filePath

curWorkBook.Close

excelFile.Quit

'Cleanup

Set curWorkBook = nothing

Set excelFile = nothing

end sub

Re: Question about exporting pivot table to excel

Tue, 26 Nov 2013 03:29:43 GMT

Thanks for your help. Try the macro, but still the same. Refer to diagram above, line between cells is missing. Anyway I could get the lines back?

Re: Question about exporting pivot table to excel

Tue, 26 Nov 2013 04:26:32 GMT

Can you upload your application

Re: Question about exporting pivot table to excel

sandeepprasad_j — Tue, 26 Nov 2013 06:40:07 GMT

Hello Yongde Li,

Check the code below ,,,

Sub PIvotExportTo Excel()

' Set Excel App

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

Set xlSheet = xlDoc.Worksheets.Add

xlSheet.Name = "Pivot Table"

' Get TB1

set obj = ActiveDocument.GetSheetObject("CH227")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

obj.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("C4") 'Paste data into cell

Hope this helps,

Thanks,

Sandeep.