LabVIEW
Excel report sheets password protect
Solved!
I'm trying to password protect Excel reports.
After several failed attempts digging down in this forum I found a possibile solution using ActiveX.
This is a test app I'm using to verify if things are working:
No errors... but nothing happens... I'm missing something?
I also tried using _Workbook.SaveAs method it worked. Bbut it behaves exactly as Save Report To File.vi (setting the password field) and this's not what I need.
To be clear I need to activate the toolbar functions Protect Sheet, ... these ... in MS Excel italian localization:
Someone can tell me if the Worksheet.Protect method does this?
Note: The excel (.xlsx) file I'm working on has just a single sheet but in production stage I may need to protect all the sheets in the workbook.
I know you've solved the problem, but in general a good place to start with Excel's automation is to do a "Record macro", then do the thing you want to automate, then stop and review the macro that it generated. (You may need to manually display the Developer tab if it's not visible by default).
It's not always 1:1 with what exactly you want to do, but it often at least provides the method you need to protect the sheet.
For this, I opened a new sheet, then recorded a macro. Once it was recorded, you can click "Macros" then "Edit" and see the VBA code it generated. In my case, it had just one line:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
That's the function you were using, but the "True" values are necessary. If you google "ActiveSheet.Protect" you get this page:
https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect
All of those parameters are Optional but you can see that if you set all of them to False, then you won't actually protect anything. In the "recorded macro" code you can see that, using the default selections, it protects shapes, locked cells, and scenarios. You can tweak those settings as needed.
Also, I've found that ChatGPT is actually very helpful in solving programming problems that you don't know how to start. The query "What ActiveX calls should I make to programmatically call the "Protect Sheet" function in Excel using LabVIEW?" gives a LOT of actually very useful information that gets you started, and you don't have to traverse the forums to find an old example.
