How to: Work with Excel files stored on Sharepoint using Qlik Application Automation

[MarkGeurtsen]

The Microsoft Excel connector in Qlik Application Automation can be used to work with Excel files stored on Sharepoint.

Sharepoint has two types of drives, these are drives attached to groups and drives attached to sites.
For performance reasons the lookup for Excel files in the Excel connector does not list these drives and only provides a lookup for Onedrive. Therefore it is necessary to obtain the ID of the drive using the list functions of the Sharepoint connector.

Obtaining drives from Sites

We have blocks to obtain all Sites that the user has access to. This block is called List Sites. After this you can perform a loop over all sites and obtain all drives attached to the site through the block List Drives from Site. Now you have a list of all the drives attached to Sites and can use the drive ID's in the Excel connector.

The following automation will list all Drives attached to a site:

Obtaining all drives from Groups

Identical to how to obtain drives from Sites, we have a block List Groups on the Sharepoint connector and a block List Drives from Group. However, when connecting the Sharepoint connector there is an option to choose between default and admin permissions. Here you will have to choose admin permissions, otherwise the List Groups block will not list any information such as group names. You will be prompted for admin consent when choosing these permissions.

The following automation will list all drives from a group.

Using the Sharepoint drive in an automation with Excel

In any block in Excel you can assign a Drive ID. As mentioned earlier you cannot make use of the lookup functionality for the drive. However you can paste an ID of a drive obtained from the other blocks. Subsequent inputs such as workbook / sheet can be used with a Lookup after the drive ID has been provided.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.