Solved

VBA: Stop the password pop up box from showing

Posted on 2011-09-09
15
963 Views
Last Modified: 2012-05-12
I have a macro that cycles thru a group of files and I'm having difficulty figuring out how to keep the password input box from popping up on the few files in the folder that are password protected.  The password input box provides two options.  Option 1 is to enter the password and gain access to make changes.  Option 2 is to click on the "Read Only" button.

I'm using Excel 2003 at my work.  

Any suggestions on how I can suppress the password input box from showing?  I've tried intentionally using a blank password in my workbook open statement and then using on error resume next.  I still seem to be unable to figure out how to stop the password box from showing.  

Any ideas would be appreciated.

Thanks,

Steve

0
Comment
Question by:ClayCanvas
  • 7
  • 7
15 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36510713
That sounds like the ReadOnly recommended password. Specify the IgnoreReadOnlyRecommended:=True argument in the Workbooks.Open method.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36510757
Try:

.Open Filename:="C:\deleteme\file.xls", ReadOnly:=True

Chris
0
 

Author Comment

by:ClayCanvas
ID: 36511666
Thanks for the feedback.  I'm cycling thru some files I need to make updates to and then save the changes.  Is there  a way to open the files I need to make changes to in a "write" mode and then somehow identify those that have passwords and skip over them??  

Thanks,

Steve
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 36511778
Not sure re 2003 but have you tried on error.  In the sample I set the password to null and then check for the wb being other than nothing.

If using in a loop do remember to set wb to nothing before looping back to the next workbook.


On Error Resume Next
Set wb = Application.Workbooks.Open("C:\Users\Administrator\Documents\EE\pw.xlsm", , , , "")
'Set wb = Application.Workbooks.Open("C:\Users\Administrator\Documents\EE\book3.xlsm", , , , "")
On Error GoTo 0
If wb Is Nothing Then Stop

Chris
0
 

Author Comment

by:ClayCanvas
ID: 36511895
Chris,

Thanks... let me go ahead and try it and I'll reply back.

Steve
0
 

Author Comment

by:ClayCanvas
ID: 36512096
Chris,

When I use the code shown below I get the password/read only pop up when opening a file where a password is required if you want to make changes.  

What happens when I run the code is first I get the popup password/read only box, if I hit "Cancel" then I get the message box indicating wb = nothing.  I'm not sure how to keep the password input box from showing up.

What are your thoughts?

Thanks,

Steve

    On Error Resume Next
        Set wb = Application.Workbooks.Open("C:\Documents and Settings\SS\Desktop\test_Excel\Shared_PW.xls", , , , "")
    On Error GoTo 0

    If wb Is Nothing Then
        MsgBox "wb = nothing"
    End If
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36512284
It worked on a test in 2010 ... so looks like it does't in 2003, and I am a long time out of 2003 so cannot provide solid help then since without a copy of 2003 to test on i'm afraid a deficient memory means I cannot provide practical help.

Chris
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36512928
OK have relocated to xp with excel 2003 and the script generally works ok, though the test had to change a bit but the snippet seems fine to me!

Chris
On Error Resume Next
        Set wb = Application.Workbooks.Open("C:\Documents and Settings\Owner\My Documents\pw.xls", , , , "")
    On Error GoTo 0

    If TypeName(wb) = "Empty" Then
        MsgBox "wb = nothing"
    End If

Open in new window

0
 

Author Comment

by:ClayCanvas
ID: 36513029
Chris,

Thanks for all effort looking into this (trying a different machine and everything).  I copy-pasted your code and changed the path and for some reason I'm getting the pop up request to either enter a password or choose "Read Only".

Is there a way to suppress all pop ups?  A way to identify whether or not a file is password protected without opening it this way or ??

Maybe I should consider going a different direction?

What are your thoughts?

Steve
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36513039
Can you upload a sample file ... remove any real data so I can see what is different about your file.
0
 

Author Comment

by:ClayCanvas
ID: 36513149
Chris,

Here are the two files I was experimenting with this morning.  The first (OpenWB) is where I copy-pasted your code.  The second is a blank file I was using for testing that has been password protected for changes.

Steve
OpenWB.xls
Shared-PW.xls
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36513236
I've saved them and I still get nothing! i.e. it works fine, albeit I needed to add the check for the nothing.
0
 

Author Comment

by:ClayCanvas
ID: 36514039
so if I understand correctly you don't get the pop up requesting either a password or the selection of "Read Only"... it goes right to the message box indicating WB = nothing?... am I understanding correctly Chris?

Steve
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36515407
You understand correctly so it would seem to be an excel install issue ... and I don't understand where there should be that difference since Ia am using your files

Chris
0
 

Author Closing Comment

by:ClayCanvas
ID: 36516524
Thanks for your time and effort looking into the problem I'm having.

Steve
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now