topic LOOP THRU CUST# LIST BOX AND PRINT SHEET in QlikView

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Wed, 30 Sep 2009 23:36:28 GMT

Want to automatically select each customer# in a LIST BOX and PRINT SHEET.

i.e. Select 1st Customer# and Print Sheet;

Select 2nd Customer# and Print Sheet;

etc.

How is this done? > MACRO?

LOOP THRU CUST# LIST BOX AND PRINT SHEET

biester — Thu, 01 Oct 2009 00:20:21 GMT

You could find a similar example here:

http://community.qlik.com/forums/p/20821/79887.aspx#79887

There we loop through an employees list box and export an object to excel. But you can replace the last lines of the macro with e. g.

ActiveDocument.Sheets(0).Print

(I didn't test latter, you should check APIGuide for correct syntax).

Rgds,
Joachim

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Thu, 01 Oct 2009 02:08:10 GMT

AWESOME!!!

Next> Want to:

1. Call QLIKVIEW from a DOS BATCH FILE with parameters

parm1: Doc Name

parm2: Customer Number

2. Open Doc; Select parm2 in Customer List Box; Print Sheet

LOOP THRU CUST# LIST BOX AND PRINT SHEET

rwunderlich — Thu, 01 Oct 2009 04:33:11 GMT

See the "Command Line Syntax" in the Ref Guide. The approach is roughly this:

qv.exe /vCustnum=123 mydoc.qvw

Assign an OnOpen macro to the doc that:

-Get's the value of the Custnum variable
-Makes the selection and prints the sheet.

Note that the variable "Custnum" must be defined in the qvw for the qvw to receive the value.

-Rob

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Thu, 01 Oct 2009 05:23:15 GMT

Just to practice with variables:

Created List Box for field BRAND then,

1. Settings / Variable Overview > Added a variable named: vBRAND & assigned value ACUVUE 2

2. Displayed vBRAND in a List Box > Shows assigned value correctly.

3. Created Macro to make a selection in the BRAND List Box

Sub FOO400
ActiveDocument.Fields("BRAND").Clear
ActiveDocument.Fields("BRAND").Select(vBRAND)
end sub

Clear works but, nothing is selected! Using : Select ("ACUVUE 2") works fine

What is wrong?

LOOP THRU CUST# LIST BOX AND PRINT SHEET

rwunderlich — Thu, 01 Oct 2009 10:58:09 GMT


ben6711 wrote: ActiveDocument.Fields("BRAND").Select(vBRAND)

You can't reference the QV variable directly in the macro. You must use an API call to retrieve it's value:

ActiveDocument.Fields("BRAND").Select(ActiveDocument.GetVariable("vBRAND").GetContent.String)

or not quite as precise, but less verbose:

ActiveDocument.Fields("BRAND").Select(ActiveDocument.Evaluate("vBRAND"))

-Rob

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Fri, 02 Oct 2009 00:28:10 GMT

Rob, Thank you!

Hope to hear more from you as I get further into the learning curve.

Ben

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Sat, 03 Oct 2009 03:18:44 GMT

RE: MACRO "FOO400b"

WORKS WHEN: 1. CALLED BY BUTTON

2. "OnOpen"

PROBLEMS WHEN CALLED FROM DOS COMMAND LINE:

1. D:\ qv.exe /vMyVarBRAND=ACUVUE D:\BEN01\QLIKVIEW\TABLEAU LaunchPad.qvw

ACUVUE IS SELECTED CORRECTLY IN LIST BOX BUT, NO PRINTING

2. D:\qv.exe /vMyVarBRAND=ACUVUE 2 D:\BEN01\QLIKVIEW\TABLEAU LaunchPad.qvw

DOC FAILS TO OPEN; TRIED: 'ACUVUE 2' and "ACUVUE 2" > NO SUCCESS

Sub FOO400b
ActiveDocument.Fields("BRAND").Clear

ActiveDocument.Fields("BRAND").Select(ActiveDocument.Evaluate("MyVarBRAND"))

activedocument.sheets("SH01").Printout
end sub

LOOP THRU CUST# LIST BOX AND PRINT SHEET

rwunderlich — Sat, 03 Oct 2009 06:26:35 GMT

If your qvw filename argument has a space in it, enclose the argument in double quotes.

"D:\BEN01\QLIKVIEW\TABLEAU LaunchPad.qvw"

-Rob

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Mon, 05 Oct 2009 23:36:29 GMT

Great! Can now successfully pass "ACUVUE 2"

Still have problem#1 > Print instruction not executed when Qlikview is started from DOS Command Line:

activedocument.sheets("SH01").Printout

Printing does happen when the macro is called by a button or, on Document Open!

LOOP THRU CUST# LIST BOX AND PRINT SHEET

rwunderlich — Wed, 07 Oct 2009 07:47:36 GMT


ben6711 wrote:on Document Open

I think you'll have to do the printing in the Document Open macro. That makes sense to me.

Also you should probably have:

ActiveDocument.GetApplication.WaitForIdle

before the print call.

-Rob

LOOP THRU CUST# LIST BOX AND PRINT SHEET

Thu, 08 Oct 2009 00:05:36 GMT

ActiveDocument.GetApplication.WaitForIdle did the trick!

-I use a PDF Print driver ("ScanSoft PDF Create" from Scan Soft PDF CONVERTER)

to create a PDF document when I "print" each sheet.

Can the passed variable appear as true text which Acrobat Reader can search on?

Currently generated PDF's are apparently graphics and Acrobat Reader has nothing to search on.

(Yes, I really need to do this)