Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro - password (Excel 2002)

    Hi!
    I have the following macro that I'd like to modify so before the file is closed it will save it with the "worksheet" name of the original file. As you can see the macro currently takes each worksheet from the main file and saves it as the worksheet name and adds the year to the end of it before it saves it. I'd like the macro to also add a password ("password to open" in the general options)... I'd like the macro to be only the worksheet name.
    Any help with the code is always appreciated!
    Thanks!!
    Lana

    Sub SaveWorksheets()
    Dim wsh As Worksheet
    Dim strDate As String

    'Turn off screen updating
    Application.ScreenUpdating = False


    For Each wsh In Worksheets
    Select Case wsh.Name

    'Don't copy & save these worksheets
    Case "pay grades", "date vlookup", "download", "translation", "COPY DATABASE"
    'Do copy & save all the other worksheets
    Case Else
    wsh.Select
    Range("A1").Select
    wsh.Copy
    With ActiveWorkbook
    'Save each worksheet with the worksheet name & 2006 as the file name
    .SaveAs Filename:=ActiveSheet.Name & " 2006" & ".xls"
    'Close the original file without saving it
    .Close SaveChanges:=False
    End With
    End Select
    Next wsh


    With ActiveWorkbook
    'Close original file without saving it
    .Close SaveChanges:=False
    End With

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - password (Excel 2002)

    An Excel file is called a workbook. One workbook can have one or many worksheets. So "the worksheet name of the file" is not a well-defined item. Can you explain what exactly you want the macro to do?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - password (Excel 2002)

    Hi Hans,
    The workbook I have has about 40 worksheets... each worksheet is the last name of an employee. Currently the macro saves all but 5 of the worksheets (named in the macro) into their own individually separate workbook/files. For example, one of the worksheets in the main workbook is "Jones", therefore the new workbook/file name is "Jones 2006". I'd like the password to open this new file/workbook called "Jones 2006" to be "jones".
    Hopes this makes better sense... sorry abou that!
    Thanks!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - password (Excel 2002)

    Change the line
    <code>
    .SaveAs Filename:=ActiveSheet.Name & " 2006" & ".xls"
    </code>
    to
    <code>
    .SaveAs Filename:=ActiveSheet.Name & " 2006" & ".xls", Password:=ActiveSheet.Name</code>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - password (Excel 2002)

    To do exactly what was requested, shouldn't the SaveAs statement be:

    <code>
    .SaveAs Filename:=ActiveSheet.Name & " 2006" & ".xls", Password:=LCase(ActiveSheet.Name)
    </code>
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - password (Excel 2002)

    Yes, thanks for that. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro - password (Excel 2002)

    Thanks Hans & Legare... both options work perfect!
    Lana

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro - password (Excel 2002)

    Note that passwords are case sensitive. If the worksheet is "Jones", my version will set "Jones" as password, and "jones" will not be accepted. Legare's modification sets "jones" as password, and "Jones" will not be accepted.

Posting Permissions

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