topic Export many reports into one Excel file in QlikView

Export many reports into one Excel file

yacine_b — Wed, 01 Dec 2010 09:44:10 GMT

Hi,

I need to export many reports into an Excel file. Each report contain only one table. Each report must be in a sheet.

could you help me to write the macro please?

Export many reports into one Excel file

Wed, 01 Dec 2010 10:38:30 GMT

Hi Yacine,
The snippet of code below should help to get you started: It opens an existing Excel file [in our case a template] and copies a couple of charts/tables from the "Exports" tab of our Qlikview app into sheet1 and sheet2 respectively of the Excel file; and then saves the Excel file in a new directory, having renamed it based on a couple of variables set up in Qlikview.

Sub Excel_Table_Export
set XLApp = CreateObject("Excel.Application")
set XLDOC = XLApp.Workbooks.Open ("C:\Templates\Test.xlsx")
XLApp.Visible = True
Const XLPasteValues = -4163

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH38").Restore
ActiveDocument.GetSheetObject("CH38").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("Sheet1")
XLSheet.Paste XLSheet.Range("A1")
XLSheet.Cells.EntireColumn.AutoFit
ActiveDocument.GetSheetObject("CH38").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH41").Restore
ActiveDocument.GetSheetObject("CH41").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("Sheet2")
XLSheet.Paste XLSheet.Range("A1")
XLSheet.Cells.EntireColumn.AutoFit
ActiveDocument.GetSheetObject("CH41").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

XLSheet.SaveAs "C:\Data\DataTest - " & [mynamevar] & " - " & [mydatevar] & ".xlsx"

set XLApp = Nothing
set XLDOC = Nothing
set XLSheet = Nothing

End Sub

I hope it helps to at least get you started!
Good luck,
Jon

Currently using Qvw 9 sr5

Export many reports into one Excel file

yacine_b — Wed, 01 Dec 2010 12:49:00 GMT

we can add "XLApp.Workbooks.Close" at the end

Export many reports into one Excel file

Sat, 22 Jan 2011 00:32:14 GMT

Hi Jon,

Quick question for you: after using XLSheet.SaveAs ... I get the popup message saying "A file named .... already exists in this location. Do you want to replace it?" How would you have the code answer yes?

Thanks for the help.

Jon

Re: Export many reports into one Excel file

Thu, 23 Jun 2011 09:34:12 GMT

I am not able to get with "set s=ActiveDocument.Sheets("Exports")".

Its giving me "error 424. Object Required."

How we can map qlikview object and access through excel file.

Thanks for your help.

Swatantra