Export many reports into one Excel file

[yacine_b]

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?

[#2 · Accepted solution]

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

[yacine_b]

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

[#4]

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

[#5]

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