Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Cool Unanswered: Using passwords in excel macros

    I am trying to create a macro that will open other password protected excel spreadsheets, update links, save and close.

    The problem is that the macro always gets hung up on the password.

    Is there a way to have the macro keyed in the password and continue?

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    Doesn't the Password Dialogue box come up when you execute your macro? It should wait until you have entered the password and then continue.

    Smitty

  3. #3
    Join Date
    Feb 2004
    Posts
    13
    The password dialog does come up, which is fine. I placed the opening of the password protected sheets at the beginning of the macro so I can start the macro, enter the password, then let the macro continue.

    However, I am trying to set up a third party scheduling program (Wintask) to run the program automatically every night at 2:00 a.m. when no one is using the spreadsheets. (If anyone is using the spreadsheets, the macro gets hung up).

    In order to run the scheduling program this way, I need it to automatically enter the passwords, because I'll be home asleep!

    Do you know of a way for the macro to key the info in without getting hung up?

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    I use this to unprotext a workbook/worksheet to make changes, and then reprotect before closing. Is this what you had in mind?


    Code:
    Sub UpdateExtraHdrFtr()
    
    'Unprotects Worksheet
        ActiveWorkbook.Unprotect Password:="password"
    
        Dim ws As Worksheet
    
        For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="password"
        Next ws
    Application.ScreenUpdating = False
    
    'Changes Headers and Footers
        Sheets("Extra").Select
        ActiveSheet.PageSetup.PrintArea = ""
        With ActiveSheet.PageSetup
            .LeftHeader = "Data Date: 02/15/2004"
            .CenterHeader = "&""Arial,Bold""&12Details"
            .RightHeader = "Print Date: &D"
            
        End With
    Application.ScreenUpdating = True
        
        Sheets("Qtr Charts").Select
    
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Posts
    13
    Thanks, Shades.

    Actually, the password that I need to insert is used to open a protected workbook. I believe that your macro unprotects the worsheet once it is open, if the workbook is protected for editing.

    The macro that I am using tries to open a protected spreadsheet, then gets hung up when the password dialog box opens.

    Your macro refere to "activesheet", but I don't think that the sheet is active until it is actually opened.

    Any ideas?


    Thanks,

    Aaron

  6. #6
    Join Date
    Feb 2004
    Posts
    3
    Use this function in your code that runs to open the protected workbook >>
    Application.Workbooks.Open (FileName,True,False,,"Password","Password", ect...)
    Good Luck
    Esam

  7. #7
    Join Date
    Feb 2004
    Posts
    13

    Smile

    Esam,

    You stated "Use this function in your code that runs to open the protected workbook >>
    Application.Workbooks.Open (FileName,True,False,,"Password","Password", ect...)"

    Sorry, I'm pretty inept at this...

    This is the part of my code that I'm trying to modify:

    Workbooks.Open Filename:= _
    "X:\BP Documents\Excel Documents\Time Sheets\BP Budgeted Hours.xls", _
    UpdateLinks:=3

    Can you help me to modify your code to open this sheet with the following parameters?:

    1) Enable Macros.
    2) Password is "Open".
    3) Automatically update links.

    If you can help me out, I'll have a major problem solved for a process that I am using at work!!!

    Thanks!!!

  8. #8
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Code:
    Application.Workbooks.Open ("X:\BP Documents\Excel Documents\Time Sheets\BP Budgeted Hours.xls", _ 
    True,False,,"Password","Password")
    The frist "True" is to Update links. As for getting around Macro Security, if you could any Virus could as well. You can set it to LOW if you trust yourself or the files, or get a Digital Certificate.

    Hope that helps,

    Smitty

  9. #9
    Join Date
    Feb 2004
    Posts
    3

    Problem Solved

    All,
    Aron sent me a private email and I answered him ..
    here are copies ..
    Thanks
    __________________________________________________ _______
    Aaron>>

    Thanks, Esam.

    It worked - you're awesome!

    Aaron
    __________________________________________________ _______
    Esam>>

    Hello,
    Here is the command line
    Application.Workbooks.Open "X:\BP Documents\Excel Documents\Time Sheets\BP
    Budgeted Hours.xls", True, False, , "open","open"
    The password is case sensitive
    Good Luck
    Esam

    __________________________________________________ _______
    Aaron>>

    >Thanks for posting, and thanks for your help! I'm not sure that you get an
    >email when I reply, so I'm sending this direct.
    >
    >Here is my reply:
    >
    >You stated "Use this function in your code that runs to open the protected
    >workbook >>
    >Application.Workbooks.Open (FileName,True,False,,"Password","Password",
    >ect...)"
    >
    >Sorry, I'm pretty inept at this...
    >
    >This is the part of my code that I'm trying to modify:
    >
    >Workbooks.Open Filename:= _
    >"X:\BP Documents\Excel Documents\Time Sheets\BP Budgeted Hours.xls", _
    >UpdateLinks:=3
    >
    >Can you help me to modify your code to open this sheet with the following
    >parameters?:
    >
    >1) Enable Macros.
    >2) Password is "Open".
    >3) Automatically update links.
    >
    >If you can help me out, I'll have a major problem solved for a process that
    >I am using at work!!!
    >
    >Thanks!!!

  10. #10
    Join Date
    Feb 2004
    Posts
    13
    Esam's code got me past the password.

    I am trying to get a scheduling program to open an excel sheet and run macros at night when no one is using the other excel files accessed by the macro.

    Unfortunately, as Smitty mentioned, I can't get a separate scheduling program (Wintask) to open the file and run a macro, because Excel doesn't seem to allow any outside program to automatically enable macros. This would allow viruses to get through.

    I am looking into a digital certificate. Does anyone know where to get one? does it cost $$$? I am assuming that if I certify the macros within excel, the "enable macros" dialog won't even come up if I list my digital certificate as a trusted source. I haven't actually tried this yet, so I don't know if it will work.

    I was also thinking of doing a self certification instead of the digital certificate, but the Microsoft executable for doing so states that other users may not recognize the certification because it could be a forgery.

    If anyone has thoughts on this, they would be much appreciated.

    Thanks,

    Aaron

  11. #11
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Your IS Department should be able to set you up with a DigitalCertificate, but a Self-Certificate may well be an option.

    Smitty

  12. #12
    Join Date
    Feb 2004
    Posts
    3

    Reset your Security

    Aaron,
    Here is an "unsafe" solution
    set the macros security option on the required workbook to low then save.
    You can do that this way ..
    Excel Menu >> Tools >> Macros >> Security >> Option 3
    That will remove the dialog and the macros will run automatically on open.
    I have not tried that opening an xls file remotly, try and tell me.
    Good Luck
    Esam

  13. #13
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Esam's alternative of setting Macro Security is unsafe unless you have a corporate level virus scan program installed.

    My IS Department has me do that for all of my users as they won't (haven't figured out how to) give me a Digital Certificate. In 4+ years in this unit it's never been a problem.

    Smitty

  14. #14
    Join Date
    Feb 2004
    Posts
    13

    Problem Completely Solved!!!

    I tried Smitty's approach:

    1) I self certified each computer on which the Macros will run.

    2) I listed the certification as a "trusted source" in each macro.

    3) I set the security level to "Medium".

    4) The first time that I opened the spreadsheet, I had to click "Always trust this source" and save the file.

    5) From then on, the "enable macro" dialog never came up again.

    Thanks for all of your help, guys!

    One more thing: I am looking for a schedule program that will open the excel file, run the macros, then save and close, and automatically notify me via email.

    I am trying "Wintask", but it does not have the capability to email me. Does anyone know of a good scheduler that will do all of the above?

    Thanks,

    Aaron

  15. #15
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Wintask - Is that the same as Programs-->Accessories-->System Tools-->Scheduled Tasks? (Sorry - I haven't used it before).

    As for the e-mail, why not send it through Excel at the end of your code. What e-mail client are you using?

    For Outlook, have a look here. It's about the most complete Outlook e-mail reference around.

    http://www.rondebruin.nl/sendmail.htm

    Smitty

Posting Permissions

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