Jump to content
SimonVal

How to protect Excel sheet

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
benners

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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)

 

  • Like 1

Share this post


Link to post
Share on other sites
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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
SimonVal

Thanks everyone for your quick replies! :D

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.