topic Re: Macro: List box Value Selection and copying report to excel then mailing in QlikView

Macro: List box Value Selection and copying report to excel then mailing

Thu, 11 Oct 2012 16:54:56 GMT

Hi Guys,

I am trying to create a macro which selects a particular value of a field, copy the pivot chart to excel and mail it to particular recepients on a Tuesday every week.

I found success in copying the pivot chart to excel and mailing it by creating a button. Inside, in Actions tab selecting External and Run macro and editing the macro.

What i am unable to do is:

How to include conditions that select value of a field, in turn the chart modifies and then mailing it.
How to automate the process, so on a Tuesday when the data reloads, last week's summary is mailed to particular persons.

Re: Macro: List box Value Selection and copying report to excel then mailing

Gysbert_Wassenaar — Thu, 11 Oct 2012 17:01:26 GMT

It's probably easier to create a bookmark instead and activite the bookmark with a OnPostReload trigger.

Re: Macro: List box Value Selection and copying report to excel then mailing

Fri, 12 Oct 2012 14:38:32 GMT

But can we create dynamic bookmarks in qlikview, since i wanted last weeks data to be sent via mail.

Re: Macro: List box Value Selection and copying report to excel then mailing

Gysbert_Wassenaar — Sat, 13 Oct 2012 18:41:16 GMT

Yes, that's possible. I think these threads can help you:

http://community.qlik.com/thread/31454

http://community.qlik.com/thread/30095

I hope this helps

Re: Macro: List box Value Selection and copying report to excel then mailing

Mon, 15 Oct 2012 12:23:29 GMT

Hi Gysbert,

Thanks for all your advice, I can now create dynamic bookmarks.

There are two things I want to do now

Check if the day is a "tue".
Check if a particular value of a field is present ...if yes..select the value and do .....if no do.....

All this i prefer to write in a macro. Can anyone hardcode it for me.

Re: Macro: List box Value Selection and copying report to excel then mailing

Gysbert_Wassenaar — Sat, 20 Oct 2012 17:26:42 GMT

If DatePart("w",Date()) = 3 then

'do tuesday stuff

Else

'do other days stuff

End if

If ActiveDocument.Fields("Month").Select "September" then

'September was selected, do the september stuff

Else

'September could not be selected, do the other stuff

End if

Re: Macro: List box Value Selection and copying report to excel then mailing

Mon, 22 Oct 2012 17:26:19 GMT

I follow this and it gives me Expected'Then' kind of error.

Re: Macro: List box Value Selection and copying report to excel then mailing

MayilVahanan — Mon, 22 Oct 2012 17:31:51 GMT

Try like this

s = ActiveDocument.Evaluate("GetFieldSelections(Month)")

if s = 'September' then

'do something

else

'do something

end if

Edit:

Sorry, i misunderstand the concept..

Hope it helps

Re: Macro: List box Value Selection and copying report to excel then mailing

Gysbert_Wassenaar — Mon, 22 Oct 2012 18:03:26 GMT

ActiveDocument.Fields("Month").Select("September") does not do what I expect (at least not in QV10, maybe in QV11 it does work). You'll need something like this instead

flag=0

ActiveDocument.Fields("Month").Select("September")

set val=ActiveDocument.Fields("Month").GetSelectedValues

for i=0 to val.Count-1

if val.Item(i).text = "September" then

flag=1

end if

if flag=1 then

'September was selected, do the september stuff

Else

'September could not be selected, do the other stuff

End if

Re: Macro: List box Value Selection and copying report to excel then mailing

Mon, 22 Oct 2012 18:03:58 GMT

Thanks Gysbert,

If DatePart("w",Date()) = 3 then

'do tuesday stuff

Else

'do other days stuff

End if

If ActiveDocument.Fields("Month").Select("September") then

'September was selected, do the september stuff

Else

'September could not be selected, do the other stuff

End if