Export table to Excel file

[NickP_DF]

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.

N.

[marcus_sommer · Accepted solution]

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

[NickP_DF]

Thank you Marcus,

I've solved thanks to your suggestion.

N.