topic Qlik Sense export to Excel macro in Integration, Extension & APIs

Qlik Sense export to Excel macro

Wed, 02 Aug 2017 13:15:25 GMT

Hi everybody,

I'm trying to write a macro to write qlik sense data in a new Excel file during load phase. I set

OverrideScriptSecurity=1

StandardReload=0

inside the Settings.ini file and I'm using these commands

Set XLApp = CreateObject("Excel.Application") ;

XLApp.Visible = True ;

inside the script, but I can't see anything working

What am I doing wrong?

Thanks a lot for your help in advance,

best regards,

Nicolò Cogno

Re: Qlik Sense export to Excel macro

tomasz_tru — Wed, 02 Aug 2017 13:41:57 GMT

You can store the data into CSV format:

STORE [table] INTO [lib:\\Library/File](txt)

Re: Qlik Sense export to Excel macro

Wed, 02 Aug 2017 15:05:46 GMT

Hi Thomas,

I knew that, but I'd like to store into XLSX file and that's why I'm trying to write a macro to do so.

Thanks

Nicolò Cogno

Re: Qlik Sense export to Excel macro

tomasz_tru — Wed, 02 Aug 2017 15:24:57 GMT

I don't think it's possible to do it in Qlik Sense. But if you give extension 'xls' to a CSV file Excel will open it

Re: Qlik Sense export to Excel macro

hoangvvo — Wed, 02 Aug 2017 18:50:45 GMT

I dont think you can do it in qlik sense, but you can execute a script if you disable the default load. I use sense server and disabling it is not an option, but i've read up the qlik help that you can execute a file from sense .. and i think you can also set sense to wait for that file to finish... i would check qlik help.

Execute C:\Program Files\Office12\Excel.exe;

Execute winword macro.doc;

Execute cmd.exe /C C:\BatFiles\Log.bat;

from : http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ScriptRegularStatements/Execute.htm

Re: Qlik Sense export to Excel macro

Thu, 03 Aug 2017 09:05:01 GMT

Thanks Tomasz

Re: Qlik Sense export to Excel macro

Thu, 03 Aug 2017 09:05:40 GMT

Thanks Hoang, I think I'm gonna write a script that will do so.

Best regards,

Nicolò Cogno

Re: Qlik Sense export to Excel macro

tomovangel — Mon, 25 Sep 2017 14:29:55 GMT

Hi Nicolo,

Did you do it ?
Because I am doing something similar, and I am lost

-Angel

Re: Qlik Sense export to Excel macro

Mon, 25 Sep 2017 14:34:13 GMT

Hi Angel,

unfortunately not . I solved with python and Qlik: using qlik store function I create csv files with the tables and then I use the execute function to launch python scripts that convert the csv to xlsx files.

Let me know if you solve in other ways!

Re: Qlik Sense export to Excel macro

tomovangel — Mon, 25 Sep 2017 14:35:36 GMT

Unfortunately I haven't used Python, so I will be searching for another workaround

Thanks

Re: Qlik Sense export to Excel macro

hoangvvo — Mon, 25 Sep 2017 15:04:08 GMT

There are ways to do this but it depends on if you want to take the time to learn. I have qlik sense server generate a csv file using store command, then have a program called Macro Scheduler that watch the folder for .csv file, reads it and convert and layout the file for me. Macro scheduler is a scripting language, that basically means you can run have any scripting language: ie. AutoHotkey is a free scripting language that reads the csv file and create an excel file: here's the link to the ahk forum: https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/

Re: Qlik Sense export to Excel macro

EricJones728 — Sat, 14 May 2022 03:44:24 GMT

Would you be willing to share your python script? Looking to do something similar.