Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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