Solved

VBA: Stop the password pop up box from showing

Posted on 2011-09-09
15
1,086 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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 Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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