topic Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document in QlikView

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

Thu, 17 Feb 2011 23:36:45 GMT

Hi...

I am trying to export a qlikview document to Excel using macros and a button. But, when I use a macro, I am only able to access the active sheets objects and not the objects on the other sheets. I tried copying the objects as bitmap images and pasting on the worksheets.

The macro is as follows:

Sub excel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible=True

set XLDoc = XLApp.Workbooks.Add

set table = ActiveDocument.GetSheetObject("CH607")

set XLSheet = XLDoc.Worksheets("Sheet1")

XLSheet.Range( "A1").value = "Order Trend"

table.CopyBitmapToClipboard

XLSheet.Paste XLSheet.Range( "A3")

set table = ActiveDocument.GetSheetObject("CH878")

table.CopyBitmapToClipboard

XLSheet.Paste XLSheet.Range( "A20")

set XLSheet = XLDoc.Worksheets("Sheet2")

set table1 = ActiveDocument.GetSheetObject("CH628")

table1.CopyBitmapToClipboard

XLSheet.Paste XLSheet.Range( "A15")

End Sub

The objects CH607 and CH878 are on the same sheet as the macro button and CH628 is on a different sheet. So, when I run the macro, the objects CH607 and CH878 are pasted on Sheet 1 and CH878 is pasted on sheet 2 as the clipboard continues to have CH878 and does not overwrite it with CH628.

Thanks for your help.

Regards

Kumar

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

Fri, 18 Feb 2011 00:23:13 GMT

You can try this macro

Function Correos()

Set Doc = ActiveDocument

Set DocProp = Doc.GetProperties

Directory = DocProp.MyWorkingDirectory

Doc.GetApplication.Refresh

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

AppExcel.WorkBooks.Add()

Set Gra01 = Doc.GetSheetObject("CH16")

Set Gra02 = Doc.GetSheetObject("CH17")

Set Txt01 = Doc.GetSheetObject("TX13")

// ***************** Se realiza filtro de Negocio y Dummy **********************************************

ActiveDocument.ClearAll false

ActiveDocument.Fields("Cia").ToggleSelect "010"

ActiveDocument.Fields("Cia").ToggleSelect "100"

ActiveDocument.Fields("Cia").ToggleSelect "500"

ActiveDocument.Fields("Cia").ToggleSelect "700"

ActiveDocument.getApplication.sleep 5000

AppExcel.Sheets.Add()

Txt01.CopyTextToClipboard

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

AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Name = "Stock PT"

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

Gra01.CopyTableToClipboard True

AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Columns("B").Delete

AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#,##0")

AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40

AppExcel.ActiveSheet.Columns("A").AutoFit

ActiveDocument.getApplication.sleep 5000

Set Gra02 = Doc.GetSheetObject("CH17")

Gra02.CopyBitMapToClipboard

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

Gra02.CopyBitMapToClipboard

AppExcel.ActiveSheet.Paste

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

// ********************* Salva y Cierra el Excel ***************************************

Ruta = Directory & "\Stock" & Year(Now) & Month(Now) & Day(Now) & " a las " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (Ruta)

End Function

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

Fri, 18 Feb 2011 00:39:31 GMT

Hi Fernando,

I tried your script but I dont know where I am going wrong. I am still unable to access the sheet object from the inactive sheet. I am just able to paste the object 607.

The script I used is as follows:

sub xl

Set Doc = ActiveDocument

Set DocProp = Doc.GetProperties

Directory = DocProp.MyWorkingDirectory

Doc.GetApplication.Refresh

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

AppExcel.WorkBooks.Add()

Set Gra01 = Doc.GetSheetObject("CH607")

Set Gra02 = Doc.GetSheetObject("CH878")

Set Txt01 = Doc.GetSheetObject("CH628")

ActiveDocument.ClearAll false

ActiveDocument.getApplication.sleep 5000

AppExcel.Sheets.Add()

AppExcel.ActiveSheet.Name = "Trend Analysis"

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

Gra01.CopyTableToClipboard True

AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Columns("A").AutoFit

ActiveDocument.getApplication.sleep 5000

Set Txt01 = Doc.GetSheetObject("CH628")

Txt01.CopyBitMapToClipboard

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

Txt01.CopyBitMapToClipboard

AppExcel.ActiveSheet.Paste

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

Ruta = Directory & "\Accent Energy" & Year(Now) & Month(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (Ruta)

end sub

Thanks for your help.

Regards

Kumar

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

Tue, 01 Mar 2011 19:32:07 GMT

Hi...

Can any one help me with this?

I am able to export the data from the current tab in qlikview to multiple sheets but not able to get the data from multiple tabs.

Regards

Kumar

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

Stefan_Walther — Sat, 12 Mar 2011 00:08:01 GMT

Have a look at the article I have posted some minutes ago:

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

Regards
Stefan

Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

chetan_surana — Fri, 20 Apr 2012 19:17:59 GMT

Hello Stefan,

I tried your script on export to excel with data and image on multiple sheets on same excel .. It works fantastic. The only problem i am encoutering is for image it only displays the active chart. I have 3 charts on the same sheet in qvw and all the 3 charts are placed in container in 3 tabs A, B, C. Now i followed your process of excel export in such a way that A with data and image is exported in one sheet , B in other and C in other . but for B and C only data is exported no image. As A is currently seelcted only A has image . If i select B tab in qvw and click on export, i see image for B tab and not in A and c ?

Can you come with a solution for this problem

Re: Exporting Objects from Multiple Sheets of a Qlikview Document to multiple worksheets of an Excel Document

stevedark — Fri, 03 Jan 2014 11:16:45 GMT

Hi there,

Stefan's blog post is an excellent example of how you can achieve this using macro code. If you want something that is more GUI and template driven you need to look at third party solutions.

NPrinting allows you to pull data from numerous tables into different tabs of an Excel template. The template can have headers and totals defined in Excel - with different font definitions and cell shading etc. You can even pull multiple tables onto one sheet or pull images from QlikView into your document.

For more information on NPrinting please see our website:

http://www.quickintelligence.co.uk/nprinting/

Regards,

Steve