Exporting qlikview document to multiple sheets in excel document.

[#1]

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

[#2]

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()

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

[#3]

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()

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

[Stefan_Walther]

Hi,

have a look at this approach:

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

Regards

Stefan

[#5]

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