Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    password protect (Excel 97)

    I need another quick Excel Lesson. I am trying to open a pre-existing
    Spreadsheet, insert some data into it and save these workbook as a new name,
    new location. This has been working great over the months, however, now the
    powers to be want it to be password protected. The code in red is the
    condition that I added to the existing statement to no avail. Please give
    me a headsup on this please.

    oleApp.workbooks.OPEN("c:PROEXPORTDATAForm3.xls",P assword="t3")


    Thanks

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    I use a macro to open a password protected workbook.
    Code looks like:
    Workbooks.Open FileName:="METC_MEARSDATAUSERSREIMERCREXCELDelimit Test2.xls", Password:="t3", WriteResPassword:="t3"
    Try it this way.
    Good luck,

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

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

    Re: password protect (Excel 97)

    The following works for me:

    <pre> oleApp.Workbooks.Open "c:PROEXPORTDATAForm3.xls", Password:="t3"
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    Legare,

    What is the oleApp (in your macro) for? I tested my macro and it worked without using oleApp.
    Just curious...

    Thanks,

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

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

    Re: password protect (Excel 97)

    I got it from the original code. I have to guess that it is an Object Variable that has been set to the Application object. However, since the original poster did not include any other code, I can't be sure. The code sort of looks like it might be being run from another app like Word or Access where that might be necessary. I removed it to test my solution.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    I guess what I am looking for is a listing of the properties that I can
    change.

    I have done the following

    oleApp=CREATEOBJ("Excel.Application")
    oleApp.Workbook.Open(<filename>)

    From there I am populating cells:

    oleApp.Cells(1,1)=<Variable>

    This all works well. The problem is when I try and apply protection to the
    sheet before I save using:

    oleApp.Activeworkbook.SAVE
    oleApp.ActiveWindow.Close
    oleApp.QUIT


    If you have a listing of the properties so that I don't spend two days going
    through the help system, this would be greatly appreciated

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    OleApp is just my name for the Excel Object as I am doing this using Office
    Automation.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    I think you need something like

    oleApp.ActiveSheet.Protect , Password:="LockUp", DrawingObjects:=True, Contents:=True, Scenarios:=True

    HTH

    Peter

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

    Re: password protect (Excel 97)

    Properties of what object? A complete list of all properties for all objects would be very large.
    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    That was it.... Thanks!!!!

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    One more thing before you get rid of me. I need to add a line (using a VB
    Button) to the entry sheet, negating the password, so that the user can add
    a line to the sheet using the button. Any ideas?

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    To unlock use
    oleApp.ActiveSheet.Unprotect ("lockUp")
    And lock it up again after adding your info.

    To find the commands to use in Excel it is generally easier to develop the macros in Excel then port them to your module by adding the oleApp prefix to the lines. Or better yet use the

    With oleApp.
    ....

    End with

    statements

    HTH

    Peter

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    Will I thought I was through...

    I have everything figured out with the exception of turning off/on the
    protection to insert a line. Below is the code that I am using, however,
    everytime it gets to the "Activecell.EntireRow.Insert" it gives me a
    protection error.

    Private Sub insertlne()

    Dim gPass As Boolean
    Dim gRow As Variant
    Dim gcount As Integer

    gcount = 1

    If ActiveWorkbook.HasPassword Then
    gPass = True
    Else
    gPass = False
    End If


    ' Turn password Off
    If gPass = True Then
    ActiveWorkbook.Unprotect ("t3")
    End If

    ActiveCell.EntireRow.Insert

    ' Format the cells from a through i

    gRow = ActiveCell.Row
    For gcount = 1 To 9
    With Application.Cells(gRow, gcount)
    .Font.Bold = True
    .Font.ColorIndex = 5
    .Locked=False
    If gcount = 9 Then
    .Value = "=SUM(E" & gRow & ":H" & gRow & ")"
    .Locked=True
    End If
    End With

    Next



    If gPass = True Then
    ActiveWorkbook.Protect Password:="t3"
    End If

    End Sub

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

    Re: password protect (Excel 97)

    The HasPassword property only tells you if you have given the workbook an open password (set using the Tools menu in the SaveAs dialog box). It does not tell you if the worksheet is protected (set with the Protect method or the protection on the worksheet Tools menu). It looks like you are trying to use the HasPassword proterty to see if the worksheet is protected.

    To check for worksheet protection you have to look at four different worksheet properties:

    ActiveWorksheet.ProtectContents
    ActiveWorksheet.ProtectDrawingObjects
    ActiveWorksheet.ProtectionMode
    ActiveWorksheet.ProtectScenarios

    Depending on how you set protection on, any combination of those properties could be set True.
    Legare Coleman

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: password protect (Excel 97)

    I will keep this code for future reference. The two main issues are 1.) Get rid of the Macro
    message and 2.) Turn off the Macro Menu option, thus not allowing those who
    have more than basic knowledge from violation the intgrety of the
    spreadsheet.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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