Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team
Authentication and Authorization
Authentication and authorization with the Microsoft Excel Connector in Qlik Application Automation works through oAuth2. When a user attempts to connect a Microsoft Excel datasource, the user will be redirected to the Microsoft login page where a user is prompted to give permission for Qlik Application Automation to access their Office 365 tenant.
The following scopes are being requested by Qlik Application Automation:
- Offline_access
- User.Read
- Files.ReadWrite.All
Working with excel worksheets
The Microsoft Excel connector has blocks available to perform actions on worksheets, data inside worksheets and tables inside worksheets. When working with data, we always recommend to work with tables rather than data on the worksheet due to ease of use.
Listing rows from Excel File
This block obtains values from an excel file worksheet from a specified range. The following input parameters are used:
- Drive ID: This is a lookup that will query a user's drive.
- Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
- Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
- Start Cell: The upper left bound cell of the range to return.
- End Cell: The lower right bound cell of the range to return.
The block returns a double nested array. The first level contains a row, the second level contains the value of a column in that row.
The same block also exists for working with tables. Here the start cell and end cell are replaced by a Table ID.
Create a new worksheet
With the block Add Worksheet to Excel File a user can add a new worksheet to an Excel document. It takes the following input parameters:
- Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
- Name: Name for the new worksheet name.
Updating Rows in a Worksheet
Using the block Update Rows in Excel Sheet you can specify a range in which you want to overwrite existing data. The following input parameters are required:
- Drive ID: This is a lookup that will query a user's drive. A sharepoint drive ID can also be provided.
- Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
- Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
- Start Cell: The upper left bound cell of the range to return.
- End Cell: The lower right bound cell of the range to return.
- Values: A double nested array containing data for each cell in the specified range. This has to match the range specified by the Start Cell and End Cell inputs. It is strongly recommended to work with variables and construct a listOfRows variable. This variable should be a list and it should be filled with multiple lists. An example could look like the following:
Create a new table
Using the block Create Excel Table With Headers a user can create a table in Excel. Using the block Add Table to Excel File Worksheet a user can also create a table without headers. The following input parameters are being requested:
- Drive ID: This is a lookup that will query a user's drive. A sharepoint drive ID can also be provided.
- Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
- Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
- Start Row: The row where the headers will be placed. Indexes in Excel start from 1.
- Start Column: Use alphabetic letter.
- End column: Use alphabetic letter.
- Headers: Array of strings to use as header values. Keep in mind that the number of columns selected using the start column and end column have to match with the headers array. E.g. C-E as column start/end for an array of 3 headers.
- Name: Name of the table for easy references.
Aside from creating a table, we can also delete is by the use of the Delete Table from Excel File Worksheet block. This requires an item ID, worksheet and Table ID.
Find Row(s) in Excel Worksheet Table
Two blocks serve to find a matching row in an Excel Table. These blocks are Find Row in Excel Worksheet Table and Find Rows in Excel Worksheet Table. The first one will only return the first row found, the second one will return all rows found matching these criteria. The following input parameters are required:
- Drive ID: This is a lookup that will query a user's drive.
- Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
- Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
- Table ID: Can be obtained using the lookup once all above fields have been filled out.
- Column Index For Search: The index of the column that is being searched for a value. The indexes are zero based.
- Search Value: The value to search for. Only fully matching values will work.
Sharing workbooks
Functionality to share workbooks is not included in the Microsoft Excel connector. These actions can be done using the Microsoft Onedrive or Sharepoint connector.
Limitations
- Cannot create a new excel file.
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.
