Replies

Hi Steve – I have a workaround. I simply rem’d the second Open command (the one with Password:= sPW2) and ran the macro. It unprotected all the files in my test sample (6) that matched password sPW1.

Then I changed the REM to the second password and re-ran the macro and cleaned up the others.

However, in an attempt to make the code more efficient I have been unable to modify your code so that the macro can try both passwords before removing the correct password. Any suggestions?

Regrettably Steve I got an Automation Error (-2147221080 stopping at wkb.SaveAs FileName:=strPath & “” & strFile, Password:=””) which I can’t find on Microsoft site.
Below is the code with my variations. Do you see any problems with it?

[Codebox]
Sub RemovePasswordToOpen()
Dim strPath As String
Dim strFile As String
Dim sPW1 As String
Dim sPW2 As String
Dim wkb As Workbook

‘Change as desired
sPW1 = “secret”
sPW2 = “secret01”
strPath = “C:Test”

On Error GoTo ErrHandler
Application.ScreenUpdating = False

strFile = Dir(strPath & “*.xls”)
Do While strFile “”
Set wkb = Nothing
On Error Resume Next
‘ Set wkb = Workbooks.Open(FileName:=strPath & “” & strFile, Password:=sPW1)
Set wkb = Workbooks.Open(FileName:=strPath & “” & strFile, Password:=sPW2)

On Error GoTo ErrHandler
If Not wkb Is Nothing Then
Application.DisplayAlerts = False
wkb.SaveAs FileName:=strPath & “” & strFile, Password:=””
Application.DisplayAlerts = True
wkb.Close (True)
End If
strFile = Dir
Loop

MsgBox “Done”

ExitHandler:
Set wkb = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

ErrHandler:
MsgBox “The error number is ” & Err & “. Its message text is: ” & Error(Err)
Resume ExitHandler
End Sub
[/Codebox]

What type of Workbook password? Is it a password to open and/or edit the file (option to set when saving the file), or the workbook password you set while in excel? Yes, the password is to open the file.

You mention that the workbooks have 1-5 worksheets. Is this relevant and how? What do you want done exactly… Sorry, this was because I was thinking of worksheets when I first posed the question.

If you were going to do this manually describe what you would do for the first 2 files in the folder… Open the first, apply the password, save the file (Save As) with the password now being blank (“”), Close the file and open the next, apply password, save the file without password and close it.

Thanks for your reply Steve; does this make it clearer?

From what I can see I need to consider the folder contents as a collection or array for VBA purposes, but I don’t know how to do that.

My apologies to all that have replied so far – I actually meant workbooks are protected. Some have one password and others another. But there are still >5000 of them and although I appreciate the code posted by sdckapr, I am unable to make it look at each workbook and then apply one of the two passwords.

And yes, ASAP Utilities would be great (for the worksheets example) but I got it wrong, Steve, I actually meant workbooks need to be looped through.

So I would appreciate more assistance (edit: and or suggestions) please.

Yes Catharine, that is exactly what I want to do and they are all in the same folder fortunately although the passwords can be one of two different PWs.

Any suggestions greatly appreciated.

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi – I have not been back for quite some time to the new Lounge and it is quite impressive.

However I find the search facility brings up too many (irrelevant) results. Therefore I would like some advice on VBA code for changing the password (sheets) in a folder. In my example there are approximately 6000 workbooks and the number of sheets varies between five and nine.

Thanks for any responses or suggestions, Leigh

Hi Hans – from a more thorough search of the Lounge I can reveal the code probably came from another source… (This was the one I was thinking of – Post: 141,597)

But I have no idea where the code came from other than it works a treat!

I went back over what you said in your first reply and will follow that path in part.

Users will still be able to select the first option (to print a label from the current workbook) and this uses exactly what you suggested, a hidden worksheet that is deleted after printing.

When they choose the second option, they will type in an ID away from the work area and some adjacent cells will be populated with a VLOOKUP formula and the results will then be printed in the same way as the first option. Finally the information that was added to the worksheet in the current workbook will be deleted and the label will be printed.

Thanks again for your suggestions.

Thanks Hans – but so that there is nothing ‘extra’ appearing (or hidden) in the current open workbook, I did not wish to create a temp worksheet but use a VBA form to display the results of the external Excel file to populate the second form in my original post.

Is it possible to paste that data into a label by double clicking the preferred label location? You may recall that you (more than) helped me with the original code that enabled the selection of the label position for which I am grateful.

In the end, when they choose the second option to print a label via the ID, I would like to have users just see a confirmation (via a form) before they printed their label. So the next question would be is it possible to pass the value in a form’s textbox to a label directly or must I do that by placing the values back into cells in the open Excel workbook?

Thanks for any insight, Leigh

Thanks Hans – I was able to use this code to do exactly what was needed. Now to go back and study what you did.

The code was surprisingly fast even when I turned on screen updating. I’ll have to learn more about it.