topic Re: Share Point in QlikView

Share Point

rajni_batra — Sat, 19 May 2012 10:41:50 GMT

Hello i am extracting an excel from share point.

when i use the URL on browser it asks me the user name and password and opens the file.

but if i use the same URL in QV it displays you are nor authorized o view this.

why so?

can anyone suggest me how to extract from sharepoint

Share Point

subin6600 — Sat, 19 May 2012 11:00:01 GMT

I also had this problem. Once try to log in seperately on the browser to the sharepoint site & try again in Qlikview.

If this is an internal sharepoint site you can add this link in the Internet options as one of your trusted sites.

This would solve your problem.

Hope some1 els have any better answer.

BR/Subin

Share Point

rajni_batra — Sat, 19 May 2012 11:05:35 GMT

no but i dont have the authority to add this as trusted site.and also this is not an internal sharepoint.

Share Point

subin6600 — Sat, 19 May 2012 11:09:13 GMT

Ok. Have you tried to login on a browser & then simulatenously check thru qlikview..

Share Point

rajni_batra — Sat, 19 May 2012 11:14:50 GMT

Have already tried this but showing the same result.

n if i try to open this URL on server its not working on browser but working only on local system what can b the reason.

Re: Share Point

subin6600 — Mon, 21 May 2012 07:35:51 GMT

Hi,

There is an option where you can download the Excel sheet using spreadsheet int he sharepoint site itself.

This will save a file with the type 'ovwssvr.iqy'. After that

We have a task for refreshing the data in Excel sheet from the SharePoint site.So we have to make some settings which would enable the Excel sheet to beRefreshed & Updated every time it opens.

To achieve this follow these steps for enabling refresh once the Excel sheet is open.

Go to Data and Click, which would open the below windowand click properties.

And check the below two options and click ‘OK’ followed by closing the previouswindow.

Save the Excel sheet and close it.

Now we need to do it from the Qlikview report side...

Create a report which reads the data from this excel and add the below conditions..

On open of the Qlikview report a Macro gets executed which has the below script.

This is a Trigger which gets executed upon Open of the report.

This ensures the Excel application has been refreshed and closes it.

A batch file is created whichlaunches the Qlikview application and the specified report.

This triggers the macro and the Excel gets refreshedand the report is reloaded and closed.

The script for the batch file is

Hope this work around helps you achieve it.

Let me know incase if any other issues

BR/Subin

Re: Share Point

subin6600 — Tue, 28 Aug 2012 06:31:01 GMT

Hi,

You could prbably change the variable that I used in here i.e., 'Test' to be replaced with any other variable.

And you need to make few settings in the macro edit script.

Shortcut for Macro Edit Script is 'Ctrl+M'

Select System Access for Requested Module Security &

Select Allow System Access for Current Local Security in the left below corner.

Then click on check on the left top corner or click on Test for checking the script.

Let me know incase if you are still finding any difficulties.

Regards,

Subin Thomas

Re: Share Point

subin6600 — Tue, 28 Aug 2012 10:11:22 GMT

it is 'test.Application.Workbooks'... please check the macro script screenshot carefully..

Regards,

Subin Thomas

Re: Share Point

subin6600 — Tue, 28 Aug 2012 10:34:44 GMT

Did you change the variable 'test' into any other name? if so please use the same in every line..

Regards,

Subin Thomas

Re: Share Point

subin6600 — Tue, 28 Aug 2012 11:07:51 GMT

Are you having access to the sharepoint site??

If yes.. when you login the session gets expired in say 5-10 mins.

Check this duration and make this program to run so frequently... that the login session never dies.. This is some kind of workaround if the access is external.

If internal sharepoint site- please add the site as local site in internet properties

and the refresh should automatically happen

Yes its working for me..

Are you having the data in Excel format at the sharepoint site?? Is it local http site or https site??

The user ID which you are trying to access is having admin rights to the site?

Rgds,

Subin

Re: Share Point

qlikviewforum — Tue, 28 Aug 2012 12:03:55 GMT

Are you having access to the sharepoint site??
If yes.. when you login the session gets expired in say 5-10 mins.
Check this duration and make this program to run so frequently... that the login session never dies.. This is some kind of workaround if the access is external.

- Yes, I have access to the sharepoint site. As I am logged into the network it doesn't need anymore authentication. But when I try to access the site in server it asks for authentication. As recommended I will try to run the program frequently!

If internal sharepoint site- please add the site as local site in internet properties
and the refresh should automatically happen

I think it is internal sharepoint site when access from my citrix machine. But when I try to access from the server it is asking for the authentication. As it is in the server I couldn't add as a local site in the internet properties.

Please let me know what can be done for this.

Are you having the data in Excel format at the sharepoint site?? Is it local http site or https site??
The user ID which you are trying to access is having admin rights to the site?

It is not in excel format, I have exported the data into excel format. It is https site and user ID which I am trying to access doesn't have admin rights ofcourse.

Please let me know if you have any more questions!

Re: Share Point

subin6600 — Tue, 28 Aug 2012 12:41:03 GMT

Please dont run this code directly in the server under QEMC.

This is a normal trick which I applied is.. I created a batch file which

1. triggers the qv report to open

2. Upon open trigger the macro (this setting in QV document itself)

3. after the macro it saves the whole data into qvd local file. (this setting in QV document itself)

4. save it & close.(this setting in QV document itself)

This batch file should be scheduled by Windows Scheduler..

With this ur server authentication is solved...

Try this and let me know if it works or else will tell you whole together another method.

I know sharepoint to qlikview is hell lot of sleepless nights that I had to get it working.

But don worry i ll make u get it working..

Regards,

Subin Thomas

Re: Share Point

qlikviewforum — Tue, 28 Aug 2012 13:14:37 GMT

Hi Subin

Thanks for explaing me the steps. I have created the batch by refering your screen shot. But the problem is there is no Windows Scheduler in my server. First I will need to resolve this before I proceed further. But there is something called task scheduler, hopefully I can use that. Can I?

Thanks for giving me some confidence. With you I am sure I will finish this task soon.

Please let me know if you have any more questions.

Thanks,

qlikview forum

Re: Share Point

subin6600 — Tue, 28 Aug 2012 13:31:32 GMT

Please follow the path

Start> Accessories> System Tools> Scheduled Tasks

This is what I referred too which is in default with every Windows OS..

Please check

Re: Share Point

qlikviewforum — Tue, 28 Aug 2012 13:37:22 GMT

Thanks! As I am using Windows Server it is there are Tasks Scheduler. Ok I will try the same as per your instructions and will update you.

Re: Share Point

qlikviewforum — Wed, 29 Aug 2012 05:48:21 GMT

Hi Subin

Just wanted to update you that I have created the batch file and trigger in the qvw. I have tried to execute the batch file manually and as expected qv.exe got lauched and qvw got opened and as triggered excel got opened and closed automatically(Though not refreshed as I have commented test.ActiveWorkbook.RefreshALL 'this gets excel sheet to be refreshed).

But the issue is qvw did not get refreshed and also the command prompt did not got closed automatically. Any help please?

Thanks,

qlikviewforum

Re: Share Point

subin6600 — Wed, 29 Aug 2012 06:06:18 GMT

The procedure that I have told you now is to read the whole data from the sharepoint site into QVD's...

Then use this qvd to read the data into the original report which is scheduled in the QEMC.

Check the below shown option for auto close after the reload.

Re: Share Point

qlikviewforum — Wed, 29 Aug 2012 06:09:58 GMT

Hi Subin

I have added exit in the last line of the batch file and managed to close the command prompt automatically. But there is something called taskeng.exe which is not getting closed(taskeng.exe: shows the lines which is there in the batch file). Any idea what is the mistake I am doing?

Thanks,

qlikviewforum

Re: Share Point

qlikviewforum — Wed, 29 Aug 2012 06:50:49 GMT

Hi Subin

To create the qvd first the document should be loaded into a table then only we can store it in a qvd right? So without reloading the qvw then how we can create the qvd.

That is not happening with these procedure.

Thanks,

qlikviewforum

Re: Share Point

qlikviewforum — Wed, 29 Aug 2012 07:16:04 GMT

Also for your information!

When I trying to refresh the excel sheet manually it is throwing the below error.

A connection to the SharePoint site cannot be established. To synchronize or refresh your table, you must be able to connect to the SharePoint site.

I think the issue is that SharePoint site needs authentication. May I know how to overcome this issue. Because if the refresh doesn't happens then there is no point in going further.

Thanks,

qlikviewforum