Protect all Excel file in a folder and sub-folder

[orla.lewsley]

Hi,

How could this macro be adapted to password protect further sub folders within sub folders and so on?

Thanks

 

[BobBridges]

Subfolders (and sub-sub-folders and so on) are usually handled by a programming technique called "recursion". A recursive program is one that calls itself, like this:

Sub Main()

  ' Determine the top folder you want to mess with.
  Set ofs = CreateObject("Scripting.FileSystemObject")
  TopFolderName = "C:\Myfolder\"
  Set TopFolderObj = ofs.Folder(TopFolderName)
  ProtectExcel TopFolderObj
  End Sub

  ' Protect all the Excel files in the specified folder.
  Sub ProtectExcel(ofo) 

    ' Identify and protect the Excel files.
    Set ofis = ofo.Files
    For Each ofi In ofis
      ' Figure out whether this file is Excel.
      If /* this is an Excel file */ Then
        ' protect it...
        End If
      Next ofi

    ' Now navigate the sub-folders.
    Set ofos = ofo.Folders
    For Each ofo In ofos
      ProtectExcel ofo
      Next ofo
  End Sub

Note where ProtectExcel calls itself, supplying a sub-folder as the folder. Warning: I haven't tested this code. But you should be able to adapt your existing program to work after this pattern.

 

[orla.lewsley]

Thanks for this, i'm new to VBA work, to adapt this code, do i just need to replace the file paths?

 

[BobBridges]

This isn't actually a working program; there are places in it where I put comments that you must replace with your own code. The code is just supposed to show you how a program can usefully call itself.

If "new to VBA code" means you've written a few programs and are still learning to do more, great; I can coach you until you can write the program you need yourself, and understand it (which is the important part). If you mean you never write VBA programs yourself and are hoping someone will write one for you, that's not me . But I don't suppose you meant that, because you already have a program that just needs to be adapted and expanded.