topic Excel Macro to pull data from Qlikview based on a parameters defined excel sheet. in QlikView

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Thu, 11 Nov 2010 21:18:56 GMT

Hi All,

I have a qvw file which is there on a server / portal available to wider audience. Basically it's a report with all data and charts. I usually have to go to that URL / Portal and load / open qvw file and then extract the data by selecting parameters for e.g. from one date to other date and for a particular region and then export it to excel which usually takes lot of time. I want to come up with a excel macro which will open the qvw file on the portal automatically and then based on the parameters .i.e. from date to date and region etc.. it will pull the data into a new or existing workbook. Since I am not very much familiar with qlikview as I have recently started using it I don't know much about the same. but any how I have done some google search and have got the below code so far with me.

Sub test2 ()
Dim doc1 As New QlikView.ActiveDocument
' ** doc1 is set to active document in open QV **
Set Appl = doc1.GetApplication
' ** Appl points at QV program **
' ** Appl.ActiveDocument points at doc1 **
Set doc2 = Appl.OpenDoc("C:\MyDoc.qvw", "", "")
doc2.Activate
' ** Appl.ActiveDocument now points at doc2 **
' ** Note difference from VBScript! **
…
End Sub

Any helps appreciated.

Thanks a lot for your help in advance.[:)]

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Thu, 11 Nov 2010 21:39:13 GMT

Check this application

http://community.qlik.com/media/p/90766.aspx

I wrote it for email distribution. You can ignore this part and use object export to Excel part of it.

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Thu, 11 Nov 2010 21:58:22 GMT

Hi Nick,

I downloaded the file email_info.xls file from the link provided by you but I couldn't find any code in the same. I checked in project explorer and I couldn't find any code module or any code in the thisworkbook or any in other sheet. I need a macro code which will automatically pull the data from a qvw file which is available on a URL / Portal and should pull the data on the basis of the parameters entered in the worksheet.

Thanks a lot for your help in advance.

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 04:13:40 GMT

here it is

http://community.qlik.com/media/p/90766.aspx

click download on a right side

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 06:10:50 GMT

Hi Nick,

I am having the similar requirement where as i want to send some of the staright tables or piviot tables as excel output and send it to respective users or need to placed in a specific folder in an automated way without the manual intervention.

I have downloaded your application to send mail of the specific objects from the multiple qvw at the same time. I want this application has to run auomatically and send the mail /distribute to the specific folder in the following way.

1. QVW files which holds the objects to be send as mail will be first reloaded.(Means the objects can be from different qvw files)

2. once all the required qvw files are reloaded.

3. Email application should run and trigger the output in mail/folder.

If possible If any objects are not properly reloaded we should have a check it is old data or the latest data.

Any suggestions to acheive the same will be helpful................

Regards,xxx

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 06:51:53 GMT

Hi Nick,

When i was trying to run the application i am getting the error failed to open document.

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 14:21:24 GMT


XXX wrote:
Hi Nick,
When i was trying to run the application i am getting the error failed to open document.

 <div></div>

You should have described in the Excel spreadsheet QV document in described Path. Before run the application you have to configure the Excel config file - email_info.xls

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 14:22:57 GMT


XXX wrote:
Hi Nick,
 I am having the similar requirement where as i want to send some of the staright tables or piviot tables as excel output and send it to respective users or need to placed in a specific folder in an automated way without the manual intervention.
 I have downloaded your application to send mail of the specific objects from the multiple qvw at the same time. I want this application has to run auomatically and send the mail /distribute to the specific folder in the following way.
1. QVW files which holds the objects to be send as mail will be first reloaded.(Means the objects can be from different qvw files)
2. once all the required qvw files are reloaded.
3. Email application should run and trigger the output in mail/folder.
If possible If any objects are not properly reloaded we should have a check it is old data or the latest data.
Any suggestions to acheive the same will be helpful................

Regards,xxx <div></div>

It's up to you how you will create a logic. All basic functionality to do this you can find in my example.

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 14:39:57 GMT

You should have described in the Excel spreadsheet QV document in described Path. Before run the application you have to configure the Excel config file - email_info.xls

Hi Nick ,

Actually whil downloading the email distribution application there was no excel file downloaded. but i have created the excel file and added the columns as it is in the appication and have configured the email_info.xls already. but still i am getting the error. Please help me

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 15:26:00 GMT

The link to the Excel file is in comments. RTFM

Excel Macro to pull data from Qlikview based on a parameters defined excel sheet.

Fri, 12 Nov 2010 16:15:06 GMT

one quick advice - check this part of my script in terms of SMTP server setup:


With objFlds
 '---------------------------------------------------------------------
 ' SMTP server details
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = userName
 .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = userPassword
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout
 .Update
 '---------------------------------------------------------------------
 End With