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?
I use this to unprotext a workbook/worksheet to make changes, and then reprotect before closing. Is this what you had in mind?
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Application.ScreenUpdating = False
'Changes Headers and Footers
ActiveSheet.PageSetup.PrintArea = ""
.LeftHeader = "Data Date: 02/15/2004"
.CenterHeader = "&""Arial,Bold""&12Details"
.RightHeader = "Print Date: &D"
Application.ScreenUpdating = True
old, slow, and confused
but at least I'm inconsistent!
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
>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
>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", _
>Can you help me to modify your code to open this sheet with the following
>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!!!
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.
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.