Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
syukyo_zhu
Creator III
Creator III

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

View solution in original post

15 Replies
syukyo_zhu
Creator III
Creator III

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.

psankepalli
Partner - Creator III
Partner - Creator III

Please share model document with dummy data.

Not applicable
Author

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
Creator III
Creator III

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
Author

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
Creator III
Creator III

which is error message?

Not applicable
Author

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
Creator III
Creator III

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
Author

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