Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unprotect many W/Books (2002/03)

    Hi:

    I'm sure I've seen this here before, but can't find it through Search; maybe someone can nudge me in the right direction.

    A colleague has about 150 Workbooks which she needs to unprotect (I'm assuming she means Passwords to Open). They are in different folders with "no consistancies in path or file names".

    Any suggestions gratefully received.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unprotect many W/Books (2002/03)

    I presume since there the files "are in different folders with 'no consistancies in path or file names'." that they also may have different passwords. Thus it would need a list of files to open and the passwords.

    If we presume that the activeworkbook has the filepaths in A2:A150 and the names in B2:B150 and the passwords in C2:C150, this code will loop through the list of files, open each one, save without a password and close the file.


    Steve

    <pre>Option Explicit
    Sub UnprotectWbk()
    Dim wkb As Workbook
    Dim rng As Range
    Dim rCell As Range
    Dim sPath As String
    Dim sFile As String
    Dim sPassword As String

    'Set range for the path
    Set rng = Range("A2:A150")

    For Each rCell In rng
    sPath = rCell.Value
    sFile = rCell.Offset(0, 1).Value
    sPassword = rCell.Offset(0, 2).Value
    Set wkb = Application.Workbooks.Open( _
    Filename:=sPath & sFile, _
    Password:=sPassword)
    Application.DisplayAlerts = False
    wkb.SaveAs _
    Filename:=sPath & sFile, _
    Password:=""
    Application.DisplayAlerts = True
    wkb.Close (False)
    Next
    Set rCell = Nothing
    Set rng = Nothing
    Set wkb = Nothing
    End Sub</pre>


  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotect many W/Books (2002/03)

    Thanks Steve, that's excellant.

    I will pass it on!
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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