topic Exporting qlikview document to multiple sheets in excel document. in QlikView

Exporting qlikview document to multiple sheets in excel document.

Thu, 10 Mar 2011 17:59:17 GMT

Hi...

I want to export the tabs of a qlikview document to sheets of a single excel document. I tried exporting using a macro wherein I was able to export the active sheet( the sheet on which the button was placed) to be exported to sheet 1 of an excel document. When I try exporting the second sheet to sheet 2(button on tab 2) it exports to a new excel sheet. Can somebody help me with this? Any suggestions would be really helpful.

Thanks.

Regards

Kumar

Exporting qlikview document to multiple sheets in excel document.

Thu, 10 Mar 2011 18:42:14 GMT

Hi Kumar,

See this example with macro.

Function ExportarSales()

Set Doc = ActiveDocument
Set DocProp = Doc.GetProperties
Directory = DocProp.MyWorkingDirectory

' Doc.GetApplication.Refresh

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

AppExcel.WorkBooks.Add()

ActiveDocument.getApplication.sleep 500
' SECOND TAB - BEGIN
' ADD SECOND TAB
AppExcel.Sheets.Add()

AppExcel.ActiveSheet.Cells(1,1) ="Nome Vendedor:"
AppExcel.ActiveSheet.Cells(2,1) ="Tipo Vendedor:"

Set Txt01 = Doc.GetSheetObject("TXT01")
Txt01.CopyTextToClipboard
AppExcel.ActiveSheet.Cells(1,2).Activate
AppExcel.ActiveSheet.Paste

Set Cs01 = Doc.GetSheetObject("CS11")
AppExcel.ActiveSheet.Cells(14,1).Activate
Cs01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

Set Gra01 = Doc.GetSheetObject("CH231")
AppExcel.ActiveSheet.Cells(4,1).Activate
Gra01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

ActiveDocument.getApplication.sleep 500

Set Gra02 = Doc.GetSheetObject("CH08")
AppExcel.ActiveSheet.Cells(9,1).Activate
Gra02.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Name = "SECOND"
AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40
AppExcel.ActiveSheet.Columns("A").AutoFit
AppExcel.ActiveSheet.Range("B4:I4").ColumnWidth = 12
AppExcel.Activesheet.Range("B4:E4").WrapText = True
AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#.##0")

AppExcel.ActiveSheet.Cells(1,1).Activate
' SECOND TAB - END

' FIRST TAB - BEGIN
' ADD FIRST TAB
AppExcel.Sheets.Add()

Set Cs01 = Doc.GetSheetObject("CS11")
AppExcel.ActiveSheet.Cells(14,1).Activate
Cs01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

Set Gra01 = Doc.GetSheetObject("CH231")
AppExcel.ActiveSheet.Cells(4,1).Activate
Gra01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste
'AppExcel.ActiveSheet.Columns("B").Delete

ActiveDocument.getApplication.sleep 500

Set Gra02 = Doc.GetSheetObject("CH08")
AppExcel.ActiveSheet.Cells(9,1).Activate
Gra02.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Name = "FIRST"
AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40
AppExcel.ActiveSheet.Columns("A").AutoFit
AppExcel.ActiveSheet.Range("B4:I4").ColumnWidth = 12
AppExcel.Activesheet.Range("B4:E4").WrapText = True
AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#.##0")

AppExcel.ActiveSheet.Cells(1,1).Activate

' FIRST TAB - END

' // ********************* Salva ***************************************

Caminho = "c:\Temp\" & "Teste " & Year(Now) & Month(Now) & Day(Now) & " as " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (Caminho)

End Function

This example, works for me.

Exporting qlikview document to multiple sheets in excel document.

Thu, 10 Mar 2011 18:42:39 GMT

Hi Kumar,

See this example with macro.

Set Cs01 = Doc.GetSheetObject("CS11")
AppExcel.ActiveSheet.Cells(14,1).Activate
Cs01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

Set Gra01 = Doc.GetSheetObject("CH231")
AppExcel.ActiveSheet.Cells(4,1).Activate
Gra01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

ActiveDocument.getApplication.sleep 500

' FIRST TAB - BEGIN
' ADD FIRST TAB
AppExcel.Sheets.Add()

Set Cs01 = Doc.GetSheetObject("CS11")
AppExcel.ActiveSheet.Cells(14,1).Activate
Cs01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

Set Gra01 = Doc.GetSheetObject("CH231")
AppExcel.ActiveSheet.Cells(4,1).Activate
Gra01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste
'AppExcel.ActiveSheet.Columns("B").Delete

ActiveDocument.getApplication.sleep 500

Set Gra02 = Doc.GetSheetObject("CH08")
AppExcel.ActiveSheet.Cells(9,1).Activate
Gra02.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Name = "FIRST"
AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40
AppExcel.ActiveSheet.Columns("A").AutoFit
AppExcel.ActiveSheet.Range("B4:I4").ColumnWidth = 12
AppExcel.Activesheet.Range("B4:E4").WrapText = True
AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#.##0")

AppExcel.ActiveSheet.Cells(1,1).Activate

' FIRST TAB - END

' // ********************* Salva ***************************************

Caminho = "c:\Temp\" & "Teste " & Year(Now) & Month(Now) & Day(Now) & " as " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (Caminho)

End Function

This example, works for me.

Exporting qlikview document to multiple sheets in excel document.

Stefan_Walther — Sat, 12 Mar 2011 00:05:48 GMT

Hi,

have a look at this approach:

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Regards

Stefan

Re: Exporting qlikview document to multiple sheets in excel document.

Wed, 29 May 2013 15:42:42 GMT

Hello Stefan,

I'm trying to run your example and I receive the following error:

Could you help me?

Many thanks

No coinciden los tipos: 'copyObjectsToExcelSheet'

sub exportToExcel_Variant2

'// Array for export definitions
Dim aryExport(2,3)

aryExport(0,0) = "CH04"
aryExport(0,1) = "RIF Allowances"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "CH02"
aryExport(1,1) = "RIF credit Requests"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

aryExport(2,0) = "LB02"
aryExport(2,1) = "Vendors"
aryExport(2,2) = "A1"
aryExport(2,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument,aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

end sub