Power Platform Community Forum Thread Details

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?

Categories:

Power Automate Desktop

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:

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.