Results 1 to 2 of 2

Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders

Threaded View

  1. #1
    Registered User
    Join Date
    01-04-2014
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    1

    Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders

    Hello Guyz,
    I need a help from those who are good at macro codes. Recently i decided to protect the excel files in my office. But its a tedious task to individually add password protection to the 1000's of excel files located in different folders and sub folders. There i started to search for suitable macro codes. And my search ended in getting a code similar to what i need. But i need to modify this code to suit it for my purpose.
    What I require :-
    1. The code should encrypt the files. The current code i recieved will only protect the files from editing. For me, files should not be viewed by anyone else with out entering the password. I want to encrypt the files
    2. I want the macro code to search for excel files in folders and Subfolders. The current code only protect those files in the folder i selected.

    So can anyone modify the following code to meet my purpose. It will be a great for me.


    The code given below will :-
    Prompt you to select a folder
    Prompt you to select PROTECT or UNPROTECT action on the files in that folder
    Prompt you to enter a password to use
    Process all the files in that folder with options above
    Give a count afterward of how many files were affected




    
    Option Explicit
    
    Sub SetProtectionInAllSheetsAllFilesInFolder()
    'JBeaucaire    3/4/2010
    'Select a folder and provide password to protect/unprotect all Excel files in folder
    Dim fPath As String, fName As String, OldDir As String
    Dim pwd As String, pwd2 As String, ws As Worksheet
    Dim Ans As Long, Cnt As Long
    
    'Folder selection
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
        End With
       
    'Choose whether to protect or unprotect the files
        Ans = Application.InputBox("Are we protecting or unprotecting the files in this folder?" & vbLf & vbLf & _
            "Enter 1 - protect files" & vbLf & "Enter 2 - unprotect files" & vbLf & vbLf & _
            "Any other value or CANCEL will abort", "Protect or Unprotect?", Type:=1)
        If Ans < 1 Or Ans > 2 Then Exit Sub
       
    'Password w/verification
        Do
            pwd = Application.InputBox("What password to use?", "Enter Password", Type:=2)
            If pwd = "False" Then Exit Sub
            pwd2 = Application.InputBox("Please enter the password again for verification?", "Re-Enter Password", Type:=2)
            If pwd2 = "False" Then Exit Sub
            If pwd = pwd2 Then Exit Do Else MsgBox "Passwords did not match, please try again"
    
        Loop
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
        OldDir = CurDir
        ChDir fPath
        fName = Dir("*.xls")
    
    'File protection
        Do While Len(fName) > 0
            Workbooks.Open fName
                For Each ws In ActiveWorkbook.Worksheets
                    If Ans = 1 Then ws.Protect Password:=pwd Else ws.Unprotect Password:=pwd
                Next ws
            ActiveWorkbook.Close True
            fName = Dir
            Cnt = Cnt + 1
        Loop
    
    ChDir OldDir
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    MsgBox "A total of " & Cnt & " files were processed."
    End Sub
    Last edited by JBeaucaire; 01-04-2014 at 08:08 PM. Reason: added missing CODE tags, please read the Forum Rules, link in the menu bar above. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Files within Multiple SubFolders and SubFolders Within It
    By codeslizer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 04:18 AM
  2. [SOLVED] VBA code to add folders and subfolders
    By rhlittau in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2013, 06:57 PM
  3. Macro to list folders and contents within it Including subfolders.
    By pekunda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2012, 08:17 AM
  4. Dir folders and subfolders macro writes over it self
    By Pero in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2009, 02:46 PM
  5. Map/List of folders, subfolders & files
    By Bogdan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-11-2006, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1