Password issue

P

Patrick C. Simonds

I have a template file which I limit access to by requiring a password to
open it. What I have done is put a button on the worksheet which allows the
user to start a new week based in the Template file. The code opens the
Template file and then renames the file based on the date the user selects
(this command button is on a dialog box with a calendar control). The
problem is, that when it opens the Template file it wants the password. How
can I get around this?





Private Sub CommandButton1_Click()

Unload SelectDate

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) +
5)

Application.ScreenUpdating = False

Sheets("Sunday").Select

Range("A2").Select

ActiveWorkbook.Save

Application.EnableEvents = False

Workbooks.Open(Filename:= _
"P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls" _
).RunAutoMacros Which:=xlAutoOpen

Application.EnableEvents = True

Sheets("Sunday").Select
Range("B3").Select

If Weekday(Calendar1.Value) <> 1 Then GoTo Error Else GoTo Continue

Continue:

Range("I1").Select

ActiveCell = Calendar1.Value

Sheets("Sunday").Range("A1001").Value = "gggg"

ActiveWorkbook.SaveAs Filename:="P:\PT_Driver_Sched\Daily Driver\ " &
Worksheets("Sunday").[I2].Value & ", " & " Week of " &
Worksheets("Sunday").[I3].Value & ", Daily Driver", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Sheets("Sunday").Select
Range("A2").Select

Application.ScreenUpdating = True

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) +
5)

GoTo EndMacro

Error:

SundayWarning.Show

EndMacro:

End Sub
 
T

Tom Hutchins

You can provide the password in your VBA code, using the Workbooks.Open method:

Workbooks.Open Filename:="D:\Data\Book1.xlt", Password:="aaa"

Hope this helps,

Hutch
 
P

Patrick C. Simonds

Tried as you sugested (code is below), but I still get asked for the
password when it opens the Workbook.


Workbooks.Open Filename:="P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls",
Password:="h2o"


Tom Hutchins said:
You can provide the password in your VBA code, using the Workbooks.Open
method:

Workbooks.Open Filename:="D:\Data\Book1.xlt", Password:="aaa"

Hope this helps,

Hutch

Patrick C. Simonds said:
I have a template file which I limit access to by requiring a password to
open it. What I have done is put a button on the worksheet which allows
the
user to start a new week based in the Template file. The code opens the
Template file and then renames the file based on the date the user
selects
(this command button is on a dialog box with a calendar control). The
problem is, that when it opens the Template file it wants the password.
How
can I get around this?





Private Sub CommandButton1_Click()

Unload SelectDate

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())
+
5)

Application.ScreenUpdating = False

Sheets("Sunday").Select

Range("A2").Select

ActiveWorkbook.Save

Application.EnableEvents = False

Workbooks.Open(Filename:= _
"P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls" _
).RunAutoMacros Which:=xlAutoOpen

Application.EnableEvents = True

Sheets("Sunday").Select
Range("B3").Select

If Weekday(Calendar1.Value) <> 1 Then GoTo Error Else GoTo Continue

Continue:

Range("I1").Select

ActiveCell = Calendar1.Value

Sheets("Sunday").Range("A1001").Value = "gggg"

ActiveWorkbook.SaveAs Filename:="P:\PT_Driver_Sched\Daily Driver\ " &
Worksheets("Sunday").[I2].Value & ", " & " Week of " &
Worksheets("Sunday").[I3].Value & ", Daily Driver", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Sheets("Sunday").Select
Range("A2").Select

Application.ScreenUpdating = True

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())
+
5)

GoTo EndMacro

Error:

SundayWarning.Show

EndMacro:

End Sub
Click to expand...
Click to expand...
 
T

Tom Hutchins

When you set the password during a Save As operation, you can set a password
to open, a password to modify, or both. The code I posted sends the password
to open the file. If you set a password to modify, you need to use the
WriteResPassword argument instead. Here is an example where both kinds of
passwords were set for the workbook:

Workbooks.Open Filename:="D:\Data\Book1.xls", _
Password:="aaa", WriteResPassword:="bbb"

My guess is that you have a password to modify set for your workbook,
instead of or in addition to a password to open.

Hutch

Patrick C. Simonds said:
Tried as you sugested (code is below), but I still get asked for the
password when it opens the Workbook.


Workbooks.Open Filename:="P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls",
Password:="h2o"


Tom Hutchins said:
You can provide the password in your VBA code, using the Workbooks.Open
method:

Workbooks.Open Filename:="D:\Data\Book1.xlt", Password:="aaa"

Hope this helps,

Hutch

Patrick C. Simonds said:
I have a template file which I limit access to by requiring a password to
open it. What I have done is put a button on the worksheet which allows
the
user to start a new week based in the Template file. The code opens the
Template file and then renames the file based on the date the user
selects
(this command button is on a dialog box with a calendar control). The
problem is, that when it opens the Template file it wants the password.
How
can I get around this?





Private Sub CommandButton1_Click()

Unload SelectDate

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())
+
5)

Application.ScreenUpdating = False

Sheets("Sunday").Select

Range("A2").Select

ActiveWorkbook.Save

Application.EnableEvents = False

Workbooks.Open(Filename:= _
"P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls" _
).RunAutoMacros Which:=xlAutoOpen

Application.EnableEvents = True

Sheets("Sunday").Select
Range("B3").Select

If Weekday(Calendar1.Value) <> 1 Then GoTo Error Else GoTo Continue

Continue:

Range("I1").Select

ActiveCell = Calendar1.Value

Sheets("Sunday").Range("A1001").Value = "gggg"

ActiveWorkbook.SaveAs Filename:="P:\PT_Driver_Sched\Daily Driver\ " &
Worksheets("Sunday").[I2].Value & ", " & " Week of " &
Worksheets("Sunday").[I3].Value & ", Daily Driver", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Sheets("Sunday").Select
Range("A2").Select

Application.ScreenUpdating = True

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())
+
5)

GoTo EndMacro

Error:

SundayWarning.Show

EndMacro:

End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Patrick C. Simonds

Thanks for your help!


Tom Hutchins said:
When you set the password during a Save As operation, you can set a
password
to open, a password to modify, or both. The code I posted sends the
password
to open the file. If you set a password to modify, you need to use the
WriteResPassword argument instead. Here is an example where both kinds of
passwords were set for the workbook:

Workbooks.Open Filename:="D:\Data\Book1.xls", _
Password:="aaa", WriteResPassword:="bbb"

My guess is that you have a password to modify set for your workbook,
instead of or in addition to a password to open.

Hutch

Patrick C. Simonds said:
Tried as you sugested (code is below), but I still get asked for the
password when it opens the Workbook.


Workbooks.Open Filename:="P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls",
Password:="h2o"


Tom Hutchins said:
You can provide the password in your VBA code, using the Workbooks.Open
method:

Workbooks.Open Filename:="D:\Data\Book1.xlt", Password:="aaa"

Hope this helps,

Hutch

:

I have a template file which I limit access to by requiring a password
to
open it. What I have done is put a button on the worksheet which
allows
the
user to start a new week based in the Template file. The code opens
the
Template file and then renames the file based on the date the user
selects
(this command button is on a dialog box with a calendar control). The
problem is, that when it opens the Template file it wants the
password.
How
can I get around this?





Private Sub CommandButton1_Click()

Unload SelectDate

Application.Wait TimeSerial(Hour(Now()), Minute(Now()),
Second(Now())
+
5)

Application.ScreenUpdating = False

Sheets("Sunday").Select

Range("A2").Select

ActiveWorkbook.Save

Application.EnableEvents = False

Workbooks.Open(Filename:= _
"P:\SPECDISP\VEH_SCHD\DailyDriverSched.xls" _
).RunAutoMacros Which:=xlAutoOpen

Application.EnableEvents = True

Sheets("Sunday").Select
Range("B3").Select

If Weekday(Calendar1.Value) <> 1 Then GoTo Error Else GoTo
Continue

Continue:

Range("I1").Select

ActiveCell = Calendar1.Value

Sheets("Sunday").Range("A1001").Value = "gggg"

ActiveWorkbook.SaveAs Filename:="P:\PT_Driver_Sched\Daily Driver\ "
&
Worksheets("Sunday").[I2].Value & ", " & " Week of " &
Worksheets("Sunday").[I3].Value & ", Daily Driver", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Sheets("Sunday").Select
Range("A2").Select

Application.ScreenUpdating = True

Application.Wait TimeSerial(Hour(Now()), Minute(Now()),
Second(Now())
+
5)

GoTo EndMacro

Error:

SundayWarning.Show

EndMacro:

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Macros 11
Drawn Objects Q 2
works in 2007 but not in 2010 0
MACRO Effeciency 3
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
VBA-User says active cell jumps to a different sheet upon Save. Please review my code 1
Workbook_BeforeSave 8
Macro to generate FileName of the source file 1

Top