Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

6 Replies
Not applicable
Author

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



Not applicable
Author

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

Not applicable
Author

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

Stefan_Walther
Employee
Employee

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

chetan_surana
Partner - Contributor II
Partner - Contributor II

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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