Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Voorburg, Netherlands
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to get multiple documents protected (Excel 2000)

    Hi, I need some help. I want to protect multiple excel documents with a password.
    Is there a script or tool available?

  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: how to get multiple documents protected (Excel 2000)

    Could be done with a macro. Could you give us a few more details?

    What are you trying to protect? All open workbooks (non-hidden workbooks)? Do you want to select workbooks from a folder, open each and protect? Prompt for password for each or do all with one prompted password, or hard code the password into the code?

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Location
    Voorburg, Netherlands
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to get multiple documents protected (Excel 2000)

    Steve,

    I want to select workbooks from a folder, open each and protect. One password would be nice.
    The documents me be only edited by people with the password.

    Roland

  4. #4
    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: how to get multiple documents protected (Excel 2000)

    This will prompt for a password, then the open file dialog box will ask for the files to protect. You can use ctrl to select individ or hold shift to select ranges of files.
    Once they are selected the files will be opened (if they already have a password you will be prompted for it), and the they will be save with the password to MODIFY the file. The files may be opened as read only (this is the same as doing SAVEAS <options> and adding a password to modify, but leaving the password to open blank.) A password to open can also be added (see HELP under SAVEAS in VB for other parameters to set)

    It then closes the file and goes to the next one until the selected files are complete

    If you want to "hardcode the password" you can eliminate the
    <pre>sPwd = InputBox... </pre>

    and replace it with the desired password (eg)
    <pre>sPwd = "drowssap"</pre>



    Steve

    <pre>Option Explicit
    Sub ProtectWorkbooks()

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim FilesToOpen
    Dim sPwd As String
    Dim x As Integer

    sPwd = InputBox(prompt:="What Password do you want to use?", _
    Title:="Password Select")

    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
    MultiSelect:=True, Title:="Files to Protect")

    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If

    x = 1
    While x <= UBound(FilesToOpen)
    Workbooks.Open FileName:=FilesToOpen(x)

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=FilesToOpen(x), _
    WriteResPassword:=sPwd
    Application.DisplayAlerts = True
    ActiveWorkbook.Close

    x = x + 1
    Wend

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler

    End Sub
    </pre>


  5. #5
    New Lounger
    Join Date
    Dec 2002
    Location
    Voorburg, Netherlands
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to get multiple documents protected (Excel 2000)

    Steve,

    Great. It works. Thanks. This makes my live a lot easier.
    I wonder now what I have to do if I want them to change using the same script or version of it. All the old documents with the same password to a new password.

    Roland

  6. #6
    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: how to get multiple documents protected (Excel 2000)

    Well I would "speculate":
    you can add a new variable (named eg "sPwdOld") and (dim it like sPwd)
    The either hard-code or prompt (just line in the current code) to give the variable a value

    Then in the workbooks, open line, add the (optional) password parameter
    <pre>Workbooks.Open FileName:=FilesToOpen(x), Password:=sPwdOld</pre>


    You might try it and see, then you could stop wondering <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2002
    Location
    Voorburg, Netherlands
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to get multiple documents protected (Excel 2000)

    Steve,

    You're again right.
    You helped me a lot.

    Thanks.

Posting Permissions

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