Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
we can add "XLApp.Workbooks.Close" at the end
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
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