Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Not applicable

Export XLS from QlikView to SharePoint via Publisher

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.

1 Solution

Accepted Solutions
disqr_rm
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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.

11 Replies
disqr_rm
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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!

Not applicable

Export XLS from QlikView to SharePoint via Publisher

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
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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.

Not applicable

Export XLS from QlikView to SharePoint via Publisher

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
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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.

Not applicable

Export XLS from QlikView to SharePoint via Publisher

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
Valued Contributor III

Export XLS from QlikView to SharePoint via Publisher

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.

Not applicable

Export XLS from QlikView to SharePoint via Publisher

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

Community Browser