topic Re: Exporting Table to Excel with help of macro in QlikView

Exporting Table to Excel with help of macro

Thu, 26 May 2016 13:04:06 GMT

Hi,

I have a straight table in a dashboard. This dashboard is client specific and is refreshed for one client at a time. I have a macro which exports the Straight table as excel. This is working fine.

I enhanced this dashboard and can run for all clients at one go using a excel file as input with client name.

But now only data for the last run client is being exported by the macro. Ideally, I want after each client run the export should happen.

Can any one help me out with this.

Qlikview code:

Client:

LOAD Client

FROM

[.\EXCEL FILES\client_list.xlsx]

(ooxml, embedded labels, table is Client);

for i = 0 to noofrows('Client')-1

let vClient = peek('Client', $(i), 'Client');

Data:

Load * from $(vClient) (txt, utf8, embedded labels, delimiter is '~', no eof) ;

Macro:

Function ExportChart()

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.GetSheetByID("SH01").Activate

output_dir="\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\Export\"

vClient=ActiveDocument.Variables("vClient").GetContent.String

Filename=vClient&".xls"

set obj = ActiveDocument.GetSheetObject("CH21")

obj.export (output_dir & Filename),"~"

end Function

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Thu, 26 May 2016 15:14:13 GMT

Hi,

when you call your function.?

If you call your function at the end of your script, you will get la last data in your export.

Hope helpful.

Re: Exporting Table to Excel with help of macro

psankepalli — Thu, 26 May 2016 15:23:42 GMT

Please share model document with dummy data.

Re: Exporting Table to Excel with help of macro

Fri, 27 May 2016 05:38:04 GMT

Yes....u are right.

So my requirement is to export for each client.

Is there a way u can suggest to do it?

Thanks

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Fri, 27 May 2016 16:24:38 GMT

You could create deux document Qv

in your first application qv

Qlikview code:

Client:

LOAD Client

FROM

[.\EXCEL FILES\client_list.xlsx]

(ooxml, embedded labels, table is Client);

for i = 0 to noofrows('Client')-1

let vClient = peek('Client', $(i), 'Client');

execute cmd.exe /c ""C:\Program Files\QlikView\Qv.exe" -l -vvClient=$(vClient) "..\..\yoursecondapplication qv";

next i

in your second application qv

Data:

Load * from $(vClient) (txt, utf8, embedded labels, delimiter is '~', no eof) ;

Macro:

Function ExportChart()

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.GetSheetByID("SH01").Activate

output_dir="\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\Export\"

vClient=ActiveDocument.Variables("vClient").GetContent.String

Filename=vClient&".xls"

set obj = ActiveDocument.GetSheetObject("CH21")

obj.export (output_dir & Filename),"~"

end Function

Re: Exporting Table to Excel with help of macro

Mon, 30 May 2016 08:46:16 GMT

Hi Xia Zhu,

Thanks for your suggestion.

I used the below script in one qv document:

Client:

LOAD Client

FROM

[.\EXCEL FILES\client_list.xlsx]

(ooxml, embedded labels, table is Client);

for i = 0 to noofrows('Client')-1

let vClient = peek('Client', $(i), 'Client');

execute cmd.exe /c "C:\Program Files\QlikView\Qv.exe"/l/v vClient='$(vClient)' "\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\POS - Rishi - Copy.qvw";

Next i;

This script is executing. But it is not reloading the 2nd qv script which we are trying to execute through command line. Can you please suggest why it is not reloading.

Do I need to specify something in the second script too?

Thanks,

Rishi

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Mon, 30 May 2016 09:23:25 GMT

which is error message?

Re: Exporting Table to Excel with help of macro

Mon, 30 May 2016 09:48:14 GMT

there is no error msg.

The loop in the 1st qv is completing for all the possible values in the table Client.

But for each value of vClient my 2nd qv should run, which is not happening.

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Mon, 30 May 2016 11:07:07 GMT

Can you check your log file of second application?

Changer la value of variable vClient and try to run

"C:\Program Files\QlikView\Qv.exe"/l/v vClient='$(vClient)' "\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\POS - Rishi - Copy.qvw" by command ligne, ans send to me screenshot

Re: Exporting Table to Excel with help of macro

Mon, 30 May 2016 11:26:13 GMT

The 2nd qv is not opening at all and it is not reloading. So log file is not getting generated.

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Mon, 30 May 2016 12:57:47 GMT

can you run "C:\Program Files\QlikView\Qv.exe" -l -vvClient='$(vClient)' "\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\POS - Rishi - Copy.qvw on commande ligne.

and send to me the result.

pay attention "C:\Program Files\QlikView\Qv.exe" -l -vvClient not /l/v

Re: Exporting Table to Excel with help of macro

Mon, 30 May 2016 13:30:54 GMT

When I am running this through command prompt, then it is running fine. But from inside the Qvw1 where we are calling it through command line it is not working.

Re: Exporting Table to Excel with help of macro

Tue, 31 May 2016 05:59:14 GMT

Hi Xia,

I got this running. Error was due to incorrect quoting for cmd.exe for multiple arguments.

When we are executing directly into cmd.exe then

"C:\Program Files\QlikView\Qv.exe"/l/v vClient='$(vClient)' "\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\POS - Rishi - Copy.qvw"

is running fine.

But when we are calling it through the qvw the we should use

execute cmd.exe /c ""C:\Program Files\QlikView\Qv.exe" -r -vvClient=$(vClient) "\\milkyway\internal\Business Value Consulting\CV Scorecards\Scorecard_Simplification\Working DND\DATA\QVW\POS - Rishi - Copy.qvw"";

We need to enclose the argument into quotes so that it runs.

Thanks Xia for your help.

Regards,

Rishikesh

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Tue, 31 May 2016 09:33:12 GMT

I'm sorry, but i don't know how can i help you.

Re: Exporting Table to Excel with help of macro

Tue, 31 May 2016 10:11:59 GMT

It is working now Xia. Thanks

Re: Exporting Table to Excel with help of macro

syukyo_zhu — Tue, 31 May 2016 12:20:36 GMT

Great.