• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protect all Excel file in a folder and sub-folder

Status
Not open for further replies.
Hello everyone,

I want to protect all excel files (including all formats) of the specific folder and sub-folder (specified by path) at one time.

Need your help guyz...

Thanks and regards,
Mehmud Khan
 

ANKUSHRS1

Member
what kind of protection you need?
you want to protect worksheet for editing or you need protect workbook from opening by other user.

pls specify.
 
Dear Ankush,

Sorry for insufficient information.

Actually I want to protect each and every excel file with the comman password.

It will take too much time to protect each and every excel file of specific folder.

I mean when someone open the excel file the it should ask for password to open.

Just want a shortcut to protect all excel file of a folder (it may 100 to 150 files) at one click.

Thanks
Mehmud Khan
 
What is this...?
I think you just gave me a protected excel file.

Let me explain what I am looking for;

- I have 10 excel files in one folder.
- All the 10 files are unprotected.
- Now, I want to protect all the excel files without opening any files.
- The password should be the same for all the excel files.

Any option is there to protect all the excel files of a folder by single VBA ?

Waiting for a positive response.

Thanks and regards,
Mehmud
 

ANKUSHRS1

Member
upload_2019-3-8_15-35-38.png

input above details like path where the excel files lie.
password for protection and password for write or modify.
 
Yes, Great it's working.

But it's only working for excel available in the specified path.

It's not working for its sub-folder.

I mean, many files are available in its sub-folder as well.

It's there any solution?

Note: What if the excel files are already protected. it's giving some error.

Thanks
 

ANKUSHRS1

Member
What do you want me to do already protected excel?
1.Can I ignore it and leave it as it was.
2. Remove old password and set common password which this macro setting for all excel files?
 
I know it very complex and time-consuming...

But it should work at least to sub-folders...

hope u can understand mu issue...

thanks in advance...

Note : In prvs solution, its also getting run on word files and getting stuck by error.
 
Last edited:
Hi,

I find the solution by using below VBA;


Public Sub addPassword()
Dim FSO As Object
Dim folder As Object, subfolder As Object
Dim wb As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
folderPath = "Z:\OFC\Audit\Data\KK"
Set folder = FSO.GetFolder(folderPath)

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With

For Each wb In folder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
Set masterWB = Workbooks.Open(wb)
ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="123"
ActiveWorkbook.Close True
End If
Next
For Each subfolder In folder.SubFolders
For Each wb In subfolder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
Set masterWB = Workbooks.Open(wb)
ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="123"
ActiveWorkbook.Close True
End If
Next
Next
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub



--

Only the problem is, it is also working to those files which having already password protection.

Could anyone please add some VBA codes to skip this script on excel files which are already password protected.

thanks in advance.

best regards,
Mehmud Khan
 

ANKUSHRS1

Member
[Public Sub addPassword()
Dim FSO As Object
Dim folder As Object, subfolder As Object
Dim wb As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
folderPath = "C:\Users\Ankush.Saeel\Desktop\New folder"
Set folder = FSO.GetFolder(folderPath)
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
For Each wb In folder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
'-------------------------------------------------
On Error Resume Next
Workbooks.Open wb, , , , "daafdsfafasfff"
If Err.Number > 0 Then
GoTo 25
End If
'-------------------------------------------------
Set masterWB = Workbooks.Open(wb)
ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="123"
ActiveWorkbook.Close True
End If
25 Next '<-------------------------------------------------
For Each subfolder In folder.SubFolders
For Each wb In subfolder.Files
If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
Set masterWB = Workbooks.Open(wb)
ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="123"
ActiveWorkbook.Close True
End If
Next
Next
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub]
 
Hi Ankush,
Thanks a lot for your help.

Actually, I am very weak in VBA, and I really not able to recode the above script.

Could you please please please make a fresh script for me which bypass to already protected files....

thanks in advance...

I know its very irritating...
 

ANKUSHRS1

Member
No problem...that is why we are here...to help each other....
I have attached file with this code. Don't hesitate if you find any flaw in this file.
 

Attachments

vletm

Excel Ninja
rizrrr
Would You reread Forum Rules:
... and You would remember ...
How to get the Best Results at Chandoo.org
  • Start a new post every time you ask a question, even if the theme is similar.
    The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
 

orla.lewsley

New Member
Hi all, would anyone know what additions would be needed to the code to enable passwords to be allocated to sub folders in sub folders and so on?
 
Status
Not open for further replies.
Top