topic Run Macro on Post Reload Dependant on Variable Being Met in QlikView

Run Macro on Post Reload Dependant on Variable Being Met

Thu, 04 Jun 2009 18:11:42 GMT

I'm struggling with a basic macro to export tables as .xls on a set day.

The .qvw is automatically reloaded overnight, I want to be able to set a date in the future that when the file is reloaded on that date the .qvw will export several tables. I've got the macro to do the export working fine and can kick it off after the reload but getting it to only do it on the day I need is tricky.

The macro I have so far is:

Sub ExportReports

if vControl <>0 then

ActiveDocument.ClearAll

ActiveDocument.Fields("Plan").Select "X"
set obj = ActiveDocument.GetSheetObject("TB01")
obj.ExportBiff "P:\ReportX.xls"

ActiveDocument.Fields("Plan").Select "Y"
set obj = ActiveDocument.GetSheetObject("TB01")
obj.ExportBiff "P:\ReportY.xls"

ActiveDocument.Fields("Plan").Select "Z"
set obj = ActiveDocument.GetSheetObject("TB01")
obj.ExportBiff "P:\ReportZ.xls"

ActiveDocument.ClearAll

End if

End Sub

Where "vControl" is a variable =if(vToday = vReportDate,1,0) with "vToday" being today's date and "vReportDate" being the desired report creation date.

I've tried various different methods but each either fails to run the macro at all or runs it regardless of the date???

Any pointers gratefully received.

Many thanks.

Run Macro on Post Reload Dependant on Variable Being Met

Thu, 04 Jun 2009 21:22:15 GMT

It seems to be working with below variable definitions -

vToday: =Today()

vReportDate: =MakeDate(2009,6,3)

vControl: =if(vToday = vReportDate,1,0)

Tested with a text object showing the value of vControl for different dates

Run Macro on Post Reload Dependant on Variable Being Met

Thu, 04 Jun 2009 21:30:56 GMT

Just a useful tip:

Be very carefull about using the function Today(), because that will always have the values of the last time the file was opened. Use Today(1) to get the current date, regardless of everything else.

Run Macro on Post Reload Dependant on Variable Being Met

Thu, 04 Jun 2009 22:03:00 GMT

Many thanks for the responses.

It wasn't the variables I was having the problems with; I can control the 'vControl' variable as required, the issue has been getting the macro 'ExportReports' to "pay attention" to the variable and only run when vControl = 1.

However I've come up with a cowboy work around:

A Sub that's called post-reload 'presses' a button object which in turn calls the ExportReports functionality in a second Sub - the button is conditional on vToday being equal to vReportDate (ie vControl = 1). If the button isn't visible the Sub initiated post-reload can't press it therefore the 2nd Sub containnig the export functionality doesn't run.

As I said "Cowboy"...

There must be a better/neater way to do it than this??

Once again; many thanks.

Run Macro on Post Reload Dependant on Variable Being Met

Fri, 05 Jun 2009 04:30:46 GMT

Hi,

Just one thing...
Can you use the variable like that??
Woluden't you use the following construct?

sub ExportReports
set v = ActiveDocument.Variables("vControl")
if v.GetContent.String <> "0" then
...

Then I would do a
"ActiveDocument.GetApplication.WaitForIdle"
after the selection and before the export... You never know...

BR
Hans