Password Protect Multiple Files

[gbc123]

Hi .. is it possible to select multiple files (via windows explorer, or having them all open in Excel) and save them all, password protected (password to open) with the same password.

This was to prevent having to open 50 files individually, save as, then choose a password from "options". I'd rather be able to select all 50 and apply the same password to them. Is this possible. Guidance appreciated.

Thanks. ray:

 

[Richie(UK)]

Hi g,

Are all 50 workbooks in the same directory (and are they the only workbooks in that directory)?

If so you could write a macro that will loop through each workbook, one at a time, and apply the password. Although the process is done 'one by one' rather than all at once it shouldn't take very long for the routine to run.

 

[gbc123]

Yes. They are all located in c:\figures folder and there are only 50 .xls documents in this location.

Your idea sounds perfect. Could you advise how to write and where to save and run it from?

Thanks.

ray:

 

[Richie(UK)]

Hi g,

Give this a whirl.

Sub OpenAndProcess()
    Dim vaFileName As Variant
    Const MyDir As String = "C:\figures"
    Const strPwd As String = "Letmein"
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyDir
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
            Application.ScreenUpdating = False
            For Each vaFileName In .FoundFiles
                ProcessData vaFileName, strPwd
            Next
        Else
            MsgBox "There were no Excel files found."
        End If
        Application.ScreenUpdating = True
    End With

End Sub

Sub ProcessData(ByVal Fname As String, Pwd As String)
    Dim wbk As Workbook
    
    Set wbk = Workbooks.Open(FileName:=Fname)
        
    With wbk
        Application.DisplayAlerts = False
        .SaveAs FileName:=Fname, password:=Pwd
        Application.DisplayAlerts = True
        .Close
    End With
    
End Sub

Change "Letmein" to whatever you want the password to be.

HTH

 

[Melimob]

Hi g,

Give this a whirl.

Sub OpenAndProcess()
    Dim vaFileName As Variant
    Const MyDir As String = "C:\figures"
    Const strPwd As String = "Letmein"
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyDir
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
            Application.ScreenUpdating = False
            For Each vaFileName In .FoundFiles
                ProcessData vaFileName, strPwd
            Next
        Else
            MsgBox "There were no Excel files found."
        End If
        Application.ScreenUpdating = True
    End With

End Sub

Sub ProcessData(ByVal Fname As String, Pwd As String)
    Dim wbk As Workbook
    
    Set wbk = Workbooks.Open(FileName:=Fname)
        
    With wbk
        Application.DisplayAlerts = False
        .SaveAs FileName:=Fname, password:=Pwd
        Application.DisplayAlerts = True
        .Close
    End With
    
End Sub

Change "Letmein" to whatever you want the password to be.

HTH

Hi There, I came across this in a search for this is exactly what I need to do however it is not working for me? It gets stuck on: With Application.FileSearch


I've changed the MyDir string to the path I need and 'letmein' to the password I want, is there anything else I needed to do to get this to work?

YOu'll have to forgive me, not very good with macros and VBA.

Many thanks

 

[Echo_Lian]

Hi Melimob,

I came across this post today as well and it worked wonderfully.

The other thing you need to change is the file path of the folder where your excel files are saved. So change the path in red to your own folder path:
Const MyDir As String = "C:\figures"

Hope this helps!

 

[botzibaby]

i'm having the same With Application.FileSearch problem...any solutions out there?
thanks!!

 

[Melimob]

Hi there, I've also done this and it's still not working. My Path is completley my own (ie.doesn't contain the word 'figures' at all).??

many thanks

 

[Gunaerson]

Bro tis is really cool but my problem is a bit different I have 40 excel workbooks in a folder, how can I password all the 40 files wit 40 different passwords all at the same time. Because opening file after file to password is a major headache please help with my problem any solution is welcomed.

 

[Terry Echols]

Hi All

This is close to what I want to do but I don't want to password protect the files, instead I want to lock them from being edited. I have searched and searched but can't find VBA code to do this.

My setup is this:

Files are in folders by year and month

Example:
Folder: 2016 Service Calls
Sub-folders: January 2016 (and all the way through the year for every year)
Files: Monday, January 4, 2016.xlsm (all of my daily files are built off of a template and the naming convention is the same through, if that matters)

So I have the same setup for 2017 (all naming conventions are the same year to year)

I want to lock from editing all the files in each MAIN then sub-folder.

So basically I have almost 365 files each in 12 sub-folders then in a main folder by year.

What would I need to edit in your code to do this?

Please help. I don't want to have to - open/protect sheet on 1000's of files, I have main folders going back to 2013.

If I should make a new thread please let me know (or point me to an on-topic thread if there is one). I know my request is a bit different than this topic but it seemed to be the closet I found to what I need to do.

Terry