topic Re: Export table to Excel file in QlikView

Export table to Excel file

NickP_DF — Sat, 11 Sep 2021 08:30:04 GMT

Hello guys,

I need to automate by using a macro the exportation of a QV pivot table with functions, numbers, text, and Excel formulas in Excel and obtain the same result as if I'd make the "Send to Excel option";

I found this solution

sub ExportToExcel()
set obj = ActiveDocument.GetSheetObject("CH136")
obj.ExportEx "D:\Qlik\test.xls", 5
end sub

But it exports all data as if they are text, so that lots of data are wrong

I've searched some documentation about the ExportEx function, but I wasn't able to find anything about 😞

Is this the right way? Am I missing something?

Thanks.

Re: Export table to Excel file

marcus_sommer — Sat, 11 Sep 2021 10:34:01 GMT

I doubt that you could get it working with the export-feature. Especially if there are various different requirements to the interpreted content and layout.

More success promises the method of copy & paste. This means creating/opening an Excel and pasting the wanted content to the intended target-areas. This allows further the possibilities to prepare the areas and/or to adjust the content afterwards if the copying doesn't fulfilled all requirements. Further it may be helpful not to transfer the origin UI objects else specialized ones from a hidden sheet to be able to use more simplified and targeted objects.

A good starting point could you find here: QlikTip #32: Exporting multiple QV objects to a single Excel document (qlikblog.at) - whereby don't use the snippets from the website else look within the download-file to see the whole code.

More easier than that could be to create an appropriate Excel file and then replacing just the source-data of it - a separate data-sheet within the Excel or maybe also using an external csv (means the Excel itself wouldn't be touched).

- Marcus

Re: Export table to Excel file

NickP_DF — Mon, 20 Sep 2021 16:10:55 GMT

Thank you Marcus,

I've solved thanks to your suggestion.