Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cmccafferty
New Contributor II

Using VBA Macro a Simple Example

I have a Macro in my Qlikview dashboard that downloads some tables to a directory in xlsx format.

I have managed to get this running fine using the OnPostReload within Document Properties, but ideally it would run during the actual reload of my dashboard i have using QMC. So that I wouldnt have to open up my dashboard and then reload the script (as it takes about an hour).

Any ideas anyone?

 

9 Replies
MVP & Luminary
MVP & Luminary

Re: Using VBA Macro a Simple Example

The qmc itself don't execute any macros directly but it could be done in this way: How-can-I-run-a-macro-on-QMC-after-reload.

- Marcus

cmccafferty
New Contributor II

Re: Using VBA Macro a Simple Example

So, am i right in thinking that I can create a bat file, to run at a specific time each morning, that will open up my QVW and reload the script??

 

MVP & Luminary
MVP & Luminary

Re: Using VBA Macro a Simple Example

Yes, that the main-logic behind it. You need to ensure that the user who runs this task has a valid licence and the proper access-rights to all sources and targets.

- Marcus

cmccafferty
New Contributor II

Re: Using VBA Macro a Simple Example

Sounds good.

Can it be deployed automatically at a certain time each morning?

Eg: It runs at 6am so that my xls tables are downloaded ready for when i come in to work.

Sorry for all the questions.

MVP & Luminary
MVP & Luminary

Re: Using VBA Macro a Simple Example

Yes, it could. You need just to trigger this task to wished time, for example per QMC with an EXECUTE statement and/or per task from windows task planner.

- Marcus

cmccafferty
New Contributor II

Re: Using VBA Macro a Simple Example

Thanks for this Marcus 🙂

My next question would be, is there a guide somewhere on how to do this?

Cheers

Chris

 

MVP & Luminary
MVP & Luminary

Re: Using VBA Macro a Simple Example

For this Qlik side could this be a starting point. If the windows task planner should be used any of the windows communities and/or google should be helpful.

- Marcus

cmccafferty
New Contributor II

Re: Using VBA Macro a Simple Example

Thanks Marcus,

I think I managed to get the correct function i needed for the bat file.

My IT guy tells me that it wont work however because our QV system is held on a different server and wont open and close the QVW unless someone is logged in.

Is this the case or have you heard of any similar cases and workarounds?

Cheers

Chris

 

MVP & Luminary
MVP & Luminary

Re: Using VBA Macro a Simple Example

Yes, this is a point on which you could easily struggle ... I assume that it could work without any logged in user but I don't know exactly which steps are required to get it.

In my case I'm always logged in because it worked in this way and after I found many years ago various workarounds I never tried it again. AFAIK the difficulties are that without a logged in user windows will use the OS default-user which needs to execute all task-parts with the appropriate access-rights to the folders/files, connections, section access and so on and needs further valid licences for Qlik, MS Office, Mail, Printer and whatever else is needed and it will also need various settings in the used tools to ensure that everything worked smoothly and without any back queries from the tools (if they should do something or if you are sure that you want to do it or something similar) - because the execution will just stop there and won't give any visible clue what's wrong (you will need to search for it within the various log-files).

If a logged in user is really a show-stopper in your environment than you would need to get the above described stuff working with a more or less trial & error approach (I assume that with some experienced support from the IT side it mustn't be too difficult).

An alternatively to the above would be to use a specialized tool like NPrinting which AFAIK also worked like the Qlik server as windows services and which don't need a logged in user (at least this proves it that there are ways to do it without a logged in user). Especially if there are plans to larger export/print/mail tasks it would be useful (and probaly also cheaper) to use such a tool as to develop and maintain all the macro-stuff.

- Marcus