Qlikview automation

[#1]

I need to create an external program that pre loads the sales data from sql server and stores it in a sales.qvd file. If I run the script from the open qvw file the data is loaded without errors, but from my vb program I am getting a HResult com error

{"Error HRESULT E_FAIL has been returned from a call to a COM component."}

here is the code from vb program

Dim QvDoc As New QlikView.ActiveDocument

QvDoc = GetObject("....\marketing_sales_data\SalesLoader.qvw")

QvDoc.Reload()

QvDoc.CloseDoc()

Basically I need to retreive data from sql server and save in sales.qvd. What is the proper way of doing this?

[Anonymous]

The proper way would be to use the command line syntax for QlikView and run a bat file or similar with:

C:\Path\Qv.exe /r C:\Path\QvFile.qvw

Another approach is to use QlikView Server/Publisher and reload on a schedule that way.

On a sidenote; to launch a QlikView instance from VBScript and run a reload you could use the following example. However, the Qv.exe /r Filename.qvw is a better solution.

rem ** VBScript **
set test= CreateObject("QlikTech.QlikView")
set newdoc = test.OpenDoc ("C:\Folder\Document.qvw","user","pass")
test.Reload
test.Save
test.Quit

[#3]

Thanks for your great reply:

I got this to work :

Dim QvApp As New QlikView.Application

Dim QvDoc As QlikView.ActiveDocument

'Load sales from sql database and save results to sales.qvd file

QvDoc = QvApp.OpenDoc("...\marketing_sales_data\SalesLoader.qvw")

QvDoc.Reload()

QvDoc.CloseDoc()

However, the script opens Qlikview and the document and the script execution window as well. However these are closed when script completes. What is annoying and I can't fix the sql connection string. I specify the user and password but the login window appears all the same. I Will try your suggestion from the command line.

Here is my connection string

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=SalesData;Password=DBPassword;Initial Catalog=SalesInformation;Data Source=SqlSeverDB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXXXXX;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is DYPHRRRXXXAA@!#$4444);

Why is the login dialogbox still requesting the user password if I am supplying it in the string and how to fix?

Thanks in advance

[#4]

Hi Johannes

Your vbscript works similar to my vb code except I have the same problem of the database credentials login dialog box that opens and request the password eventhough I am supplying it in the connection string.

I have an idea, is it possible to create a sales.qvd file from vb using ole. This way I would execute the query from vb code and save results to the sales.qvd file without openning the qvw file?

[#5]

Here is my workaround for the login credentials dialog box.

In my VB prog I connect to my sql server and execute my query and save the dataset to a csv file

then I open my qvw document and load the contents of the csv and store it in the qvd file. I will post some sample code here shortly

[#6]

How can we make the vbscript execution wait for the document reload to finish? Currently when I execute the vbscript, it completes immediately but the Qlikview App reload is still executing.

[Pravinkarne]

It is possible to fully automate reports generation from Qlikview (Both PDF Aand Excel)

See below link - 

https://youtu.be/GtYxpd7WUbs