Help with ExportToExcel Macro in QlikView

RJW — Wed, 18 Jan 2023 16:39:46 GMT

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!

Re: Help with ExportToExcel Macro in QlikView

Albert_Candelario — Wed, 18 Jan 2023 19:32:26 GMT

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

topic Re: Help with ExportToExcel Macro in QlikView in QlikView

Help with ExportToExcel Macro in QlikView

Re: Help with ExportToExcel Macro in QlikView