Share via

Excel Services Query against Protected Sheets

Anonymous
2019-12-10T21:09:35+00:00

I am posting this question here and in Nintex community as well since I don't know where the issue lies. I have a Nintex 2013 workflow that is using Query Excel Services action to read certain cells in an Excel file and store the value in a variable. Everything worked great during test until the owner of the Excel template protected the sheets so users' couldn't overwrite any formulas. After that, I kept receiving "Error querying Excel Services. We're sorry, we couldn't open your workbook" error on my Nintex workflow. Is there a way to use Excel Query Services against protected cells with formulas? Maybe grant access in the Excel template to the farm credentials used in the query? My query control is setup as follows:

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-12-10T23:51:13+00:00

    Hi. Tony

    I'm Robert, an Independent Advisor trying to help.

    In that case, because the protection of spreadsheets is a two-step process: the first step is to unlock the cells that other users can edit and then you can protect the spreadsheet with or without a password.

    Step 1: Unlock the cells that have to be editable

    1. In the Excel file, select the spreadsheet tab you want to protect.
    2. Select the cells that other users can edit.

    Tip: To select several non-contiguous cells, press Ctrl + left click.

    1. Right-click anywhere on the sheet and select Format Cells (or use Ctrl + 1 or Command + 1 on the Mac computer). Then go to the Protection tab and disable Locked.

    Step 2: Protect the spreadsheet

    Then, select the actions that users should be able to perform on the sheet, such as, among others, insert or delete columns or rows, edit objects, sort or use the automatic filter. In addition, you can also specify a password to lock the spreadsheet. A password prevents other people from removing the protection from the spreadsheet: it is necessary to type it to unprotect the sheet.

    See below the steps necessary to protect the sheet.

    1. On the Review tab, click Protect Sheet.
    2. In the Allow users of this spreadsheet list, select the items that you want users to change.

    Regards,

    Robert.

    Was this answer helpful?

    0 comments