Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I currently have a macro connected to a button in QlikView that exports 3 objects to Excel (pivot table, straight table, and a current selections box). The code and button work as intended, but I would like to export the 3 objects all to the same Excel file, rather than 3 separate ones.
I've tried to create a subroutine that attempts to do this but am getting an error that I can't quite seem to fix. Also, I've tried using the CopyToClipboard method but I am having similar issues. The ExportToExcel is the only method that seems to work for me.
Working code that exports the objects to 3 sperate files:
Sub Export
Set Chart = ActiveDocument.GetSheetObject("RAW")
Set P = Chart.GetProperties
Chart.SendToExcel
Set Chart = ActiveDocument.GetSheetObject("PIVOT")
Set P = Chart.GetProperties
Chart.SendToExcel
Set Chart = ActiveDocument.GetSheetObject("CS08")
Set P = Chart.GetProperties
Chart.SendToExcel
End Sub
Testing export to 3 separate sheets in the same file (not working):
Sub Export_Testing
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
'Create a new workbook
Set objWorkbook = objExcel.Workbooks.Add()
'Create a new sheet for the table object
Set objSheet1 = objWorkbook.Sheets.Add()
objSheet1.Name = "RAW"
Set Chart = ActiveDocument.GetSheetObject("RAW")
Set P = Chart.GetProperties
Chart.SendToExcel objSheet1.Range("A1")
'Create a new sheet for the pivot object
Set objSheet2 = objWorkbook.Sheets.Add()
objSheet2.Name = "PIVOT"
Set Pivot = ActiveDocument.GetSheetObject("PIVOT")
Pivot.SendToExcel objSheet2.Range("A1")
'Create a new sheet for the CS08 object
Set objSheet3 = objWorkbook.Sheets.Add()
objSheet3.Name = "Filters"
Set CS08 = ActiveDocument.GetSheetObject("CS08")
CS08.SendToExcel objSheet3.Range("A1")
End Sub
The error message comes along during the first attempt at sending the RAW table to Excel. It gives the following error: "Wrong number of arguments or invalid property assignment: 'Chart.SendToExcel'". I should also mention I have given the QlikView file full security access in the document properties, CTRL + Shift + M settings have been allowed full system access, and this has been verified in the module menu as well.
Any help would be greatly appreciated!
Hello @RJW ,
Thanks for posting.
What is the business case here?
Shouldn't this be done via NPrinting on-demand reports or regular reports that would give you more features, flexibility and have a wider use cases for your end-customer.
Cheers,
Albert