Locking columns in excel output

[carterfleming]

Is there a way for Alteryx to lock certain columns in excel output files so that they can't be edited? I'm not talking about locking the workflow, I mean the output files I have to produce need certain columns locked for editing upon output.

[cjaneczko]

I believe you would need to have a VBA macro in the excel file that runs to lock the specific columns. Then you'd have to create a CMD .bat file that executes the VBA macro in excel. Alteryx can then link to that .bat with the Run Command tool. I don't know if the functionality exists within Alteryx to lock the columns without this.

[ArnaldoSandoval]

Hi @carterfleming 

You may consider creating an Excel Macro that protect the cells you want, this article may help you Triggering Excel Macros from the comfort of your Alteryx Workflow 

I also found an article here in the community which I liked but I can find it now, from that article I downloaded the attached solution. 

hth

Arnaldo

[Clifford_Coon]

Hi @carterfleming ,

There one way to accomplish this.

It's a little involved but should work for you.

Start with a Template file that is already protected.

The use the Blob tools to make a bit for bit copy under a new name.

Run Workflow to update the new output file.

- Alteryx will update the field even if they are protected in Excel.

Example attached.

I use a macro I created called Blob_Copy:

 Sample workflow attached.

Happy Solving 

 

[carterfleming]

This looks like you're trying to read locked cells. That's the opposite of what I'm trying to do. I'm trying to take unlocked cells and lock them.

[ArnaldoSandoval]

Hi @carterfleming 

Overview General:

• Alteryx does not have the capabilities to protect Excel's cells, sheets and/or workbook, as Excel it is not their product.
• What we could do is write a VBA macro to an Excel workbook that will protect cells, sheets and/or workbook.
• Unfortunately you did not describe the Excel file you want to apply protection, we will supply one.

Solution Overview:

• We create the Excel file: My_Excel_File.xlsx, it contains some data shown below at its worksheet: Sheet1, and we will protect few of them.

Notes:

1. The yellow shadowed cells are the protection target.
2. The attached Excel files do not have the highlight.

• We will write a VBA macro into this Excel file that will apply the protection shown above.
• We named the VBA macro Protect.
• We saved the Excel workbook, with its macro as Template.xlsm.
• Now the Excel file type end with XLSM because it contains a macro; this is by Excel handling of workbooks with macros.
• The macro Protect protects the cells shown above (those highlighed in yellow); you will have to modify the macro to protect the cells in your Excel file. You did not shared details of your Excel file, and the cells you want to protect.
• The workflow creates the file: OutputExcel.xlsm (because we do not know the name of your file), write to this file some data, and run its macro Protect. via the Run_Macro_Script.vbs
• This is the workflow's canvas; note: the workflow name is: Protect_Excel_01.yxmd

Solution's Components:

• These are the components:

1. Protect_Excel_01.yxmd; this is the Alteryx workflow protecting an Excel file.
2. My_Excel_File.xlsx; this should be your Excel file, I do not know its name of any detail.
3. My_Excel_File.xlsm; Once we added the Protect macro to the XLSX file we must save it as an XLSM.
4. Protect this is the VBA macro embeded into the XLSM that actually protect the required cells.
5. OutputFile.xlsm; this is the output file the scripts create, we do not know the name of your Excel file.
6. Run_Macro_Script.vbs; this is a Visual Basic Script run by the workflow; this vbscript actually run the Protect macro found at the OutputExcel.xlsm file.

• The objects with red highlights are the ones you have to customise to your needs, which are unknwon to us.

Protect VBA code:

• The Excel file features a shortcut CTRL + p for the Protect macro, this is for testing purposes.
• The VBA Protect macro has 3 sections: I, II and III in the image below:

1. Section I: Select the cells you want to protect, by default the Template.xlsm has all its cells un-protected (Your file should do the same); You should modify this part of the VBA to suit your needs; it defines two ranges of cells to protect: F1:H14 and A1:A14
2. Section II: Applies the password to the defined ranges create above (step a);  the passwords are: "alteryx" and "a2"; you should customise this section accordingly to your needs, as well with the passwords.
3. Section III: This statement save the OutputExcel.xlsm with the protection defined on sections I and II.

The MsgBox is commented out, if you keep it, the Alteryx workflow will hang in that message.

Run_Macro_Script.vbs:

• This is the script the workflow: Protect_Excel_01.xlsm run to automatically apply protection to the OutputExcel.xlsm file.
• It is shown below:

• When implementing this script, you should align the names appearing in the red boxes; if you changed the name of the OutputExcel.xlsm file, amend it in the script, and if you rename the Excel's VBA macro, then fix the name as highlighted.

-/-

I will resume/continue this reply on a second post, as this is very long.

hth

Arnaldo

[ArnaldoSandoval]

Hi @carterfleming 

This is the continuation of my reply, I got a weird message, and I do not want to lost a very long reply.

Warnings:

• Please do not run the Protect macro directly on the Template.xlsm file, if you do, then you have to undo it (not that easy for changes done via VBA); if you want to do that, create a copy and run it on the copy, remember: ctrl + p run the macro.
• Verify that all the cells in your Excel file are configured to none protection; the "Protection" tab on the Format Cells should be un-checked.
  
• The screenshot above is for these cells:

• The Protection Completed popup message is not displayed, its line was commented out in the VBA script, remember to comment it off when deploying to production.

• Once you run the Workflow, open the output file: OutputExcel.xlsm, if you try to modify any of the protected cells, you will get a message like the one below:

Remember this sample implemented the passwords: alteryx and a2.

• If you rename some of the objects referenced by the workflow, these are the tools you should adjust with the new names.

hth

Arnaldo

[ArnaldoSandoval · Accepted solution]

Hi @carterfleming 

I am attaching the workflow Protect_Excel_01.yxmd with its dependencies to this reply:

hth

Arnaldo