Solved

VBA: Stop the password pop up box from showing

Posted on 2011-09-09
15
922 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 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

18 Experts available now in Live!

Get 1:1 Help Now