Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Exporting Table to Excel with help of macro

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

Tags (1)
1 Solution

Accepted Solutions
syukyo_zhu
Contributor III

Re: Exporting Table to Excel with help of macro

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

15 Replies
syukyo_zhu
Contributor III

Re: Exporting Table to Excel with help of macro

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.

Partner
Partner

Re: Exporting Table to Excel with help of macro

Please share model document with dummy data.

Not applicable

Re: Exporting Table to Excel with help of macro

Yes....u are right.

So my requirement is to export for each client.

Is there a way u can suggest to do it?

Thanks

syukyo_zhu
Contributor III

Re: Exporting Table to Excel with help of macro

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

Not applicable

Re: Exporting Table to Excel with help of macro

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

syukyo_zhu
Contributor III

Re: Exporting Table to Excel with help of macro

which is error message?

Not applicable

Re: Exporting Table to Excel with help of macro

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.

syukyo_zhu
Contributor III

Re: Exporting Table to Excel with help of macro

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

Not applicable

Re: Exporting Table to Excel with help of macro

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