topic Re: Macro to export multiple charts to Excel in QlikView

Macro to export multiple charts to Excel

Wed, 25 Nov 2020 16:16:04 GMT

I've done a search on the forum and came across a document which details the exporting of multiple charts to Excel (in my example below, 4 in total), using the following macro (this is exactly how it is in the Edit Module)...

sub exportToExcel_VoidDetail

'// Array for export definitions
Dim aryExport(3,3)

aryExport(0,0) = "objNewVoids"
aryExport(0,1) = "New Voids - To Be Categorised"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "objVoidDetailNonRental"
aryExport(1,1) = "Non-Rental Void Detail"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

aryExport(2,0) = "objVoidDetailRental"
aryExport(2,1) = "Rental Void Detail"
aryExport(2,2) = "A1"
aryExport(2,3) = "data"

aryExport(3,0) = "objVoidDetailNonResidential"
aryExport(3,1) = "Non-Residential Void Detail"
aryExport(3,2) = "A1"
aryExport(3,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

end sub

I've created a button object with the action 'Run Macro' and the Macro Name of 'exportToExcel_Void Detail.

I'm not familiar with macros and am obviously missing something because when I click the button object, instead of running the macro (as I would expect it to), it opens the Edit Module.

Can someone review the above and let me know what the exact script should be?

Many thanks in advance.

Re: Macro to export multiple charts to Excel

Chanty4u — Wed, 02 Mar 2016 12:10:08 GMT

hope this

Re: Exporting multiple sheet objects from the multiple sheets?

QlikTip #32: Exporting multiple QV objects to a single Excel document

Re: Macro to export multiple charts to Excel

marcus_sommer — Wed, 02 Mar 2016 12:12:42 GMT

You need to use the complete macro and not the few code-snippets which are shown as example. On the end of this article is a download-link: QlikTip #32: Exporting multiple QV objects to a single Excel document

- Marcus

Re: Macro to export multiple charts to Excel

Wed, 02 Mar 2016 12:19:06 GMT

Thanks for that tip there Marcus. Like I say, I have no idea when it comes to macros... including the full text works. Many thanks

Re: Macro to export multiple charts to Excel

vinesh_panchal — Fri, 23 Nov 2018 17:13:51 GMT

Hi,

I've noticed that if you try Text Objects, instead of exporting it, it pastes whatever is on your clipboard, so it doesn't seem to be copying the text objects?

Please help

Thanks

Vinesh

Re: Macro to export multiple charts to Excel

Ankit007 — Tue, 20 Aug 2019 11:30:26 GMT

Could you please guide how to save the exported document automatically?

Re: Macro to export multiple charts to Excel

vinesh_panchal — Tue, 20 Aug 2019 11:46:32 GMT

In the macro you indicate in which document you want it to go into, so you can save a template excel document and use that to export into and that can be saved in the folders you wish for it to be stored

Re: Macro to export multiple charts to Excel

vinesh_panchal — Tue, 20 Aug 2019 11:49:00 GMT

I've found a way to export text objects, you have to store the text in a variable and copy the content of the variable into the cell you wish for it to be stored then format it as per how you've got it formatted. A bit long winded but works for me.

Re: Macro to export multiple charts to Excel

Ankit007 — Tue, 20 Aug 2019 11:53:29 GMT

Thanks for replying, I am using ActiveWorkbook.SaveAs Filename:= "C:\new.xlsx" to save the workbook but it is not working and getting a msg "Macro parsed failed. Functionality was lost".