Export XLS from QlikView to SharePoint via Publisher

[#1]

I am using QlikView Publisher to execute a reload via batch script as I need QlikView to automatically extract an Excel file after the reload is completed. I want the Excel file to be written to a SharePoint site.

The file is successfully written to the SharePoint site when I reload the app under my ID and the ID that is used to run the Publisher XS.

However, the file is not written when I run the batch file using a External Application Task from within Publisher.

There appears to be a security consideration that I am missing, so any insight that you can provide would be greatly appreciated.

[disqr_rm]

How are you moving file from QV server to the SharePoint document library? Just a copy command over network path? Or you are calling SP webservices to store excel file?

You know you can always assign an email address to a document library and email excel file an an attachment. It will then extract the file from email and store it in the document library. You can also configure to use subject line to create a sub-directory and then store file in there. It's SP standard feature. Thought you would find it interesting!

[#3]

I am using a macro from within QlikView to complete the export (OnPostReload event). I use Publisher to call a batch file that completes the reload with QlikView Developer since Publisher will not run the OnPostReload macro.

sub ExportToXLS()

ActiveDocument.ActiveSheet.SheetObjects("TBL01").ExportBiff "\\sharepoint.domain.com\sites\doclibrary\dataextracts\data_extract_01.xls"

end sub

The export command works if I run the extract from within QlikView Developer, but will not work when I have Publisher run the reload. Thoughts?

P.S. - I'll give the email to SharePoint idea a try and see if that will work. Thanks!

[disqr_rm · Accepted solution]

Well, Publisher and Macros together cannot be trusted - as it is clearly documented in the documentation as well.

Why don't you call this batch file from QV script using "execute" command? Give it a try. Something like this: EXECUTE batchfile.bat;

You can always put all the copy commands, what you have in batch file, in your QV script itself by using EXECUTE CMD.EXE..... But there is a security restrictions copying files over UNC path. Please make sure to make adjustments in the server registry to work with UNC paths from command prompts. Here is the link for this adjustment details: http://support.microsoft.com/kb/156276

Hope this helps.

[disqr_rm]

Oh! I think problem is Excel file not being generated from Macro, and not the copy itself. I think I overlooked the problem. Let me think about it.

[#6]

Rakesh, for testing purposes, I have the macro generate two Excel files. One is supposed to go the SharePoint site, the other is supposed to go to folder on the server (via UNC path). The file is successfully saved to the server but the SharePoint file never shows up.

[disqr_rm]

Did you give "publisher service user" write access on the SharePoint directories / shares? For testing purpose, see if you can add "Publisher User" as local admin on the SP server.

[#8]

I did give the Publisher user access as an Admin on the SharePoint site. If I login as the Publisher user to the server that Publisher runs on, I can save to SharePoint. I just can't get the External Application Task (batch script) to actually write to the SharePoint site.

[disqr_rm]

See if the registry adjustmens makes any difference: http://support.microsoft.com/kb/156276

You would need to make this change in publisher server where batch file is being executed.

[#10]

The registry adjustment seemed to do the trick. The file is now being created. Thank you for your assistance!