Solved

VBA: Stop the password pop up box from showing

Posted on 2011-09-09
15
1,163 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

695 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