How to protect Excel sheet

[SimonVal]

Being a newbie on this forum, I couldn't find any topic on this.

I've got a Excel file with one tab containing output from an AutoIT-script. I want to protect this tab against editing. The code I does set the attributes right, but doesn't set the password. In VBA this works great.
The code:

With $objExcel.ActiveSheet.Protect
  .DrawingObjects = True
  .Contents = True
  .Scenarios = True _
  .AllowFormattingCells = True
  .AllowFormattingColumns = True
  .AllowFormattingRows = True
  .AllowSorting = True
  .AllowFiltering = True
  .AllowUsingPivotTables = True
  .Password = "123"
EndWith

Any suggestions?

[DynamicRookie]

• Right click a worksheet tab.
• Click protect sheet
• Enter a password
• Check the actions you will able the users of your sheet to perform
• Click Ok
• Confirm the password and click Ok
• Done!

If you need further help, reply me

[SimonVal]

Thank you, DanyamicRookie for your fast reply,

Your suggestion works manually in Excel. I would like AutoIT to perform the actions. Or am I missing something in your answer?

[DynamicRookie]

10 minutes ago, SimonVal said:

Thank you, DanyamicRookie for your fast reply,

Your suggestion works manually in Excel. I would like AutoIT to perform the actions. Or am I missing something in your answer?

AutoIt works with the Mouse and the Keyboard

 

So making AutoIt to do it is literally the same thing that doing it manually.

[benners]

just a guess but does this work?

With $objExcel.ActiveSheet
  .DrawingObjects = True
  .Contents = True
  .Scenarios = True _
  .AllowFormattingCells = True
  .AllowFormattingColumns = True
  .AllowFormattingRows = True
  .AllowSorting = True
  .AllowFiltering = True
  .AllowUsingPivotTables = True
  .Protect("123")
EndWith

 

[DynamicRookie]

Just now, benners said:

just a guess but does this work?

With $objExcel.ActiveSheet
  .DrawingObjects = True
  .Contents = True
  .Scenarios = True _
  .AllowFormattingCells = True
  .AllowFormattingColumns = True
  .AllowFormattingRows = True
  .AllowSorting = True
  .AllowFiltering = True
  .AllowUsingPivotTables = True
  .Protect("123")
EndWith

 

You missed a _ after .scenarios = true

 

.Scenarios = True _

.Scenarios = True ^Error

 

Bad formatted

 

[benners]

I just used the code from post #1, maybe that's part of the problem 

[DynamicRookie]

5 minutes ago, benners said:

I just used the code from post #1, maybe that's part of the problem 

The main post code is VBScript.

[JLogan3o13]

38 minutes ago, DynamicRookie said:

AutoIt works with the Mouse and the Keyboard

So making AutoIt to do it is literally the same thing that doing it manually.

AutoIt does a whole lot more than just Mouse and Keyboard manipulation.

@SimonVal I just took a quick glance at your OP, as I am on my phone. For setting a password on a sheet, this is how I normally do it:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Copy.xlsx")
Local $oSheet = $oWorkbook.ActiveSheet

    $oSheet.Protect("Password1")

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

 

[benners]

20 minutes ago, DynamicRookie said:

The main post code is VBScript.

it also works in AutoIt

#include <Excel.au3>

Local $o_Excel = _Excel_Open()
Local $o_Workbook = _Excel_BookOpen($o_Excel, @DesktopDir & "\Book1.xlsx")

With $o_Workbook.ActiveSheet
    .DrawingObjects = True
    .Contents = True
    .Scenarios = True
    .AllowFormattingCells = True
    .AllowFormattingColumns = True
    .AllowFormattingRows = True
    .AllowSorting = True
    .AllowFiltering = True
    .AllowUsingPivotTables = True
    .Protect("123")
EndWith

_Excel_BookClose($o_Workbook)
_Excel_Close($o_Excel)

 

[jdelaney]

Just so you know, the 'protection' on an excel page will only stop a less than knowledgeable person.  There are easy to find excel macros that will unlock any given excel file.  I know, because I had to unlock one such document that someone forgot the password to.

Edit:  Maybe security has been buffed in recent years, no clue.

Edited January 4, 2018 by jdelaney

[DynamicRookie]

1 minute ago, jdelaney said:

Just so you know, the 'protection' on an excel page will only stop a less than knowledgeable person.  There are easy to find excel macros that will unlock any given excel file.  I know, because I had to unlock one such document that someone forgot the password to.

Edit:  Maybe security has been buffed in recent years, no clue.

It's not possible anymore, sadly.

[jdelaney]

Good for MS, they did something right! 

[JLogan3o13]

20 minutes ago, DynamicRookie said:

It's not possible anymore, sadly.

This is categorically false. With the most recent versions of Office and their reliance on an underlying XML structure, it is easier than ever to break even a strong password on a worksheet. Both the hash and the salt are stored in the header of the worksheet itself, and easily readable.

[SimonVal]

Thanks everyone for your quick replies! 

The code from JLogan3o13 works fine:

$oSheet.Protect("Password1")

I know there are programs/scripts/macros out there to unlock a sheet. However, I'm using the protection to prevent 'accidental' editing.