Output of XLSX file creating password protected cells

Question

I've searched all over the forum and cannot locate anything, so hoping someone has a solution or has experience with this! TIA

We have a template file that was given to us by the accounting firm that is used to process a filing. There’s multiple tabs, the first tab has 2 dropdowns that then dictate the headers of the other 2 data tabs (Headers are formula driven). When we write to these tabs, the cells that have data in them become password protected (locked). Blank cells do not. (For instance, if i only write to cells D2:D3, cell E2 is fine and same with D4.

We use alteryx blob tool to create a copy of the template (and rename it based on entity name). And then we use the data output tool to write to range starting with the 2nd row of the data input tabs (so we do not touch the headers). We’ve tried both the writing with formatting saved box checked and unchecked (no difference in result).

If we were to manually reperform this, and value paste the data into those same 2 data tabs, we have no issues with the pw protection/locking.

Is there any functions in alteryx that differ from a human manually copying and value pasting the data into those cells? Or is there something in the Alteryx process that would be involving this password protection? Note that the file itself has password protected locations in general, however, if manually done- there's no issues, so nothing should be triggering the protection.

DavidP · Answer

Hi @alee282

This seems like a weird one. I assume you can't share the template and we can't reproduce the issue without it.

There's obviously a few different things happening and just to make sure that none of that is causing the problem, it would be good to check them off.

1. Making a copy of the template with the blob tool and renaming it. Disable all other steps after this point and then do them manually. i.e. don't use the template file received from your client to manually paste the values, take the copy produced buy the workflow. This is to eliminate that part of your workflow as the culprit.

2. Assuming that the above did not pinpoint the issue, you might have to look at the excel file. A bit of Googling suggests that the "Normal" style in Excel can cause this if protection is enabled in the Excel file, but not the specific cells in the sheet. One option would be to write your values to a different (hidden) sheet and use mapping formulas in excel to copy the values to the range you want. This is not a great option if the range is very large, as it results in a lot of reference formulas that makes the file very large.

3. If all else fails you can also consider running a VBA script from Alteryx to unlock the cells as an after run event, but it's another ugly workaround.

As mentioned at the start, it would be easier to help if you could share a version of the template for us to play with.