Hi!
I am using Power Automate for Desktop, and I am not using actions which are referred to Excel. Instead I am using SQL queries, but since the worksheets are protected, I need first to unprotect them to do SQL query, am I right?
Would the VB Script be helpful? If yes, what should I write to enable (unprotect the Sheet)?
I also want to protect the Sheet at the end, how should I do that? With VB Script?
Yes, you can use VBScript to unprotect a worksheet or workbook (depending on what's protected). For example, to unprotect an entire workbook, you can use this:
Share
Report
All responses (
Answers (Set Excel = CreateObject("Excel.Application")
Set Workbook = Excel.Workbooks.Open("{YourExcelWorkbookPath}")
Workbook.Unprotect {Password}
Workbook.Save
Workbook.Close
Replace the placeholders in curly brackets with actual values.
If you want to unprotect a single protected worksheet, you would need to use an extra step, such as this:
Set Excel = CreateObject("Excel.Application")
Set Workbook = Excel.Workbooks.Open("{YourExcelWorkbookPath}")
Set Worksheet = Workbook.Worksheets("{WorksheetName}")
Worksheet.Unprotect {Password}
Workbook.Save
Workbook.Close
You would then need to use the exact same procedure with a .Protect method afterwards to protect your workbook/worksheet again after you're done. The .Protect method has more parameters that can be passed, depending on what you want to protect (i.e. how much you want to allow the user to do, if you're only protecting a worksheet and not an entire workbook). See here for reference: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
#1
WarrenBelz
146,670
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,011
Most Valuable Professional
