Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

QlikView automation by external VBScript scripts

vadimtsushko
Not applicable

QlikView automation by external VBScript scripts

You can do all sorts of administrative tasks with QlikView applications through Automation API.

Export charts to excel or csv files, export variables from application, set sheet level security parameters before deploying application or warm your application on server by automatically opening some sheets and selecting some most popular values in listboxes.

In most cases I would code such a task not as a macro inside QlikView application but as an separate external VBSCript file ...

Labels (3)
Attachments
Comments
cristiand
Not applicable

Nice article,

I find this when I was looking for automation with VBS. I'm trying to run a vbs from External Task (publisher) and i'm not able to do it from there. I created a task that calls a bat file and from this bat file a vbs is run. The bat works great when I execute it manualy, but when is launch from publisher nothing happends...the task runs, the bat file is executed and when it's suppose to call the vbs nothing happen, the task remains under execution and bat file is "hang" in the vbs execution.

The vbs is just for open / close documment to fire a macro.

If the vbs code is something basic like "Dim Arg1" the execution runs smoothly, but as soon as I add the line "set qv = CreateObject("QlikTech.QlikView")" it stops working when is executed from Pusblisher by mean of a bat file.

Do you know that could be wrong? did you experience this problem?

Thanks

Cristian

vadimtsushko
Not applicable

Hi cristian.

Actually I myself did not used such vsb scripts from Publisher, but I guess same problems bugs many scripts which runs by any windows service scheduler. There is many aspects worth to pay attention in deploying such scripts.

- First of all you should check windows account who own publisher distribution service. It can be `Local service account` for example which have no access to desktop. Try to set your account as owner of service for the debugging purpose.

- Make sure your scripts do not create any dialog windows. For example ReloadEx command has parameter to supress reload progress windows (I would not recommend reload from script anyway)

- If your script freeze on `CreateObject("QlikTech.QlikView")` you can open document in qlikview from command line before that command. For example in first line of your bat file you can put something like `"c:\Program Files\QlikView\qv.exe" ABSOLUTE_PATH_TO_YOUR_DOCUMENT\YOUR_DOCUMENT.qvw`. Then CreateObject command would use that appplication. I saw this technique somewhere on internet. It was recommended when user has similiar problems with automating Excel from scheduler. I've tried it with windows scheduler service - it worked for me.

cristiand
Not applicable

Hello Vadim,

Thanks for your quick response.

I also don't like the idea to reload from batch. I'had situations where QV hangs after saving the file (apparently solved in SR6 or SR7 for what I saw in release notes).

The QV services run under administrator account and I'm pretty sure no dialog exist when opening qv or any document. I've also assign a QV Call to that account to avoid license problems while testing this.

I will try your aproach of opening the QV document first with a batch and then try to execute the vbs code to see what happen.

For now I come up with a similar aproach that seems to work. Since I don't want to reload the application (but I need to close it after opening it) I changed the batch script of qv.exe /r "C:\....qvw" to just load the app (qv.exe /vvClose=1 "c:\...qvw") and then a macro is executed on onOpen event. This macro quit qlikview if variable vClose is 1.

For now this seems to work from Publisher external task.

Regards,

Cristian

vadimtsushko
Not applicable

Yes, I saw this approach (macro triggerred on onOpen event and command line params) before. Specifically in Tim Benoit comments to hist post: `qlikviewmaven dot blogspot dot com /2008/08/qlikview-command-line-and-automation.html`

It works well but I prefer to keep automation code external to qlikview application.

vadimtsushko
Not applicable

Take a look at exended version of QvUtils.vbs (For now I've added it as separate attached file to oriinal post)

It's QlikView class has two boolean property `forceOpen` and `forceKill`

If `forceOpen` set to `True` script open qlikview document trough shell command before creation of automation object.

If `forceOpen` set to `True` - class use `pskill` tool to kill qlikview process in Quit method.

You should set two constant for qlikview executable path and pskill executable path at top of the file.

It works in my environment:

cristiand
Not applicable

Thanks a lot Vadim,

This looks promising!. My main concern was that something fails and I end up with a lot of QV process without finish and this file you sent me solves that problem. I will test it in this week!

vadimtsushko
Not applicable

Please share your experience, when you'll do.

Not applicable

Thanks for sharing.

FYI - I think there is a small bug in your QVUtils.vbs.

I think the following line:

    set mApp = Nothing

Should read:

    set m_App = Nothing

vadimtsushko
Not applicable

Thank you for pointing me at it. I think I'll fix and integrate that extended version of QVUtils in original sample.

james
Not applicable

All, anyone know why I am getting "Object Required:ActiveDocument...If I restart the PC, it runs once fine...

Set MyApp = CreateObject("QlikTech.QlikView")

Set MyDoc = MyApp.OpenDoc ("c:\Test.qvw","","")

Set ActiveDocument = MyDoc

ActiveDocument.GetSheetObject("BU1").Press

MyDoc.CloseDoc

Set MyDoc = Nothing

MyApp.Quit

Set MyApp = Nothing

11-9-2014 1-08-27 PM.jpg

Version history
Revision #:
1 of 1
Last update:
‎07-20-2014 12:53 PM
Updated by: