Solved

VBA: Stop the password pop up box from showing

Posted on 2011-09-09
15
1,004 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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