Using passwords in excel macros

aaroncoppersmit · #1 (**permalink**) 02-19-04, 17:08

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?

Smitty · #2 (**permalink**) 02-19-04, 17:24

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

aaroncoppersmit · #3 (**permalink**) 02-19-04, 18:08

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?

shades · #4 (**permalink**) 02-20-04, 10:27

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

aaroncoppersmit · #5 (**permalink**) 02-20-04, 11:06

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

e_jaber · #6 (**permalink**) 02-22-04, 00:44

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

aaroncoppersmit · #7 (**permalink**) 02-23-04, 11:23

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!!!

Smitty · #8 (**permalink**) 02-23-04, 18:08

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

e_jaber · #9 (**permalink**) 02-25-04, 14:55

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!!!

aaroncoppersmit · #10 (**permalink**) 02-25-04, 15:11

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

Smitty · #11 (**permalink**) 02-25-04, 15:19

Your IS Department should be able to set you up with a DigitalCertificate, but a Self-Certificate may well be an option.

Smitty

e_jaber · #12 (**permalink**) 02-25-04, 15:55

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

Smitty · #13 (**permalink**) 02-25-04, 15:59

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

aaroncoppersmit · #14 (**permalink**) 02-25-04, 16:04

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

Smitty · #15 (**permalink**) 02-25-04, 16:10

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****/sendmail.htm

Smitty

Internet Services

Web Development

Digital Marketing

Consumer Tech