Sign in to follow this  
Followers 0
Santana

AutoProtecting Excel Worksheets from Autoit

5 posts in this topic

Hi guys,

I was assigned a project where upper management will log in to a form and make a request for reports. then my unit will create the report ans save it on a network shared directory.

I though about having a MS Access Database with 3 forms (request, upload and report viewer/search)

  • Management will fill out the request form form and when they save/close the form, an email is sent to our unit with a generated projectid and requestor email information.
  • we will then create the report, open the upload form which will move the report to a shared directory with a new filename and store a link on a table field.
  • By using the projectid and the requestor email information, an email is autogenerated advising that the report is available for review
  • The requestor will then login and review the report.
My challenge is that my boss wants the files protected from being edited. I thought about pdf. However, most files are too wide to fit into a single page which eliminates this approach.

I wonder if there is a way to (from Autoit) open or move a file, protect all the cells and assign passwords to unprotect, then save the file. This way dont have to manually protect files before they're moved to the shared directory.

I will really appreciate any input.


Just another special date with a different challenge

Share this post


Link to post
Share on other sites



This protects all sheets in a workbook:

#include <excel.au3>
$oExcel = _ExcelBookOpen("C:\temp\test.xls")
For $oSheet In $oExcel.ActiveWorkbook.Worksheets
    $oSheet.Protect("Test")     ; Protect 
;   $oSheet.UnProtect("Test")   ; Unprotect 
Next
_ExcelBookSave($oExcel)
Exit

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Water,

Thanks for the reply. There is another situation, the excel reports are divided by tabs in the workbook and tabs. Each tab is a subsection of the same report and it contains the title for the specified tab (and that is unknown to the user uploading the files). Can we include a way to identify each tab by its name? I see that the sample code include a tab named "test". Is there any way to retrieve the name of each tab so we can supply it in the script as an argument?

Thanks again


Just another special date with a different challenge

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

... I see that the sample code include a tab named "test" ...

The example code protects/unprotects all sheets of the test.xls worksheet.

What you see in the code as "test" is the password to protect/unprotect the sheet.

What do you mean by "tab" - the sheets the workbook consists of?

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes. I wrote tabs by I meant worksheet. I will give it a try and see.


Just another special date with a different challenge

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
Sign in to follow this  
Followers 0