Hi

My work has about 500 excel files with the same password to edit the files.
I have the password and I need to open each file and save it without the
password so another application can read them. The other application can
read only excel that doesnt have passwords.

Does anyone know an efficient way to do this?

Doug

Gordon, the code below will allow you to browse and choose any number of
files and once you've completed that part (by clicking [Cancel] in the file
open dialog), it will begin opening those files using the password set in the
code and then saving them back to their original location without a password.
They will have the same name.

Without more specifics, I can't get much more 'efficient'. If all of the
workbooks were in a single folder, the 'get filenames list' part of the code
could do it without you having to choose them one by one, but I'm assuming
they're scattered about in various folders.

To put the code into use: create a new workbook and then open the VB Editor
using [Alt]+[F11]. Choose Insert | Module from the VBE menu toolbar. Copy
and paste this code into the module, change the Const masterPassword to
contain the proper password, then you can either press [F5] to run it right
there, or close the VB Editor and use Tools | Macro | Macros to run the code.
Your screen will remain unchanging while the process takes place, and a
message will appear when it has all been accomplished.

Sub OpenAndRemovePasswords()
Const masterPassword = "myPassword"
Dim filesList() As String
Dim anyFilename As Variant
Dim lCount As Integer

ReDim filesList(1 To 1) ' initialize

'get list of files to process
'will loop until [Cancel] is used
anyFilename = "kickstart" ' just to initialize the loop
Do While anyFilename < ""
anyFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If anyFilename < False Then
If filesList(UBound(filesList)) < "" Then
ReDim Preserve filesList(1 To UBound(filesList) + 1)
End If
filesList(UBound(filesList)) = anyFilename
Else ' was false, no file chosen
anyFilename = ""
End If
Loop
Application.ScreenUpdating = False
For lCount = LBound(filesList) To UBound(filesList)
If filesList(lCount) < "" Then
Workbooks.Open Filename:=filesList(lCount), _
Password:=masterPassword
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=filesList(lCount), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
MsgBox "All files have had their password removed"
End Sub


"gordon" wrote:

Hi

My work has about 500 excel files with the same password to edit the files.
I have the password and I need to open each file and save it without the
password so another application can read them. The other application can
read only excel that doesnt have passwords.

Does anyone know an efficient way to do this?

Doug

gordon,
I misread your post - but not to worry! you said the workbooks have a
password required to edit them. I misread and thought it needed one to open
the workbook.

But long story short, the code will do that also - the .Open actually
ignores the password:=masterPassword when that is done to a file without a
password required to open the file. Then the .SaveAs code clears both any
password required to open the file OR to edit it. So code should work even
if I have actually misinterpreted your post.

However, if the protection is at a different level: the workbook or
worksheets are simply password protected, then we need to add some more code
in between the .Open and the .SaveAs.

I suggest trying it on one file, then see if that file has been properly
altered with regards to its protection. If it hasn't, post back and tell
what process you have to use to get it unprotected as needed, such as
Tools | Protection |Unprotect | Workbook/Worksheet
and we'll be able to do that for you.

Also, if all files happen to be in one location, mention that and we can
save a lot of file choosing by hand.

"JLatham" wrote:

Gordon, the code below will allow you to browse and choose any number of
files and once you've completed that part (by clicking [Cancel] in the file
open dialog), it will begin opening those files using the password set in the
code and then saving them back to their original location without a password.
They will have the same name.

Without more specifics, I can't get much more 'efficient'. If all of the
workbooks were in a single folder, the 'get filenames list' part of the code
could do it without you having to choose them one by one, but I'm assuming
they're scattered about in various folders.

To put the code into use: create a new workbook and then open the VB Editor
using [Alt]+[F11]. Choose Insert | Module from the VBE menu toolbar. Copy
and paste this code into the module, change the Const masterPassword to
contain the proper password, then you can either press [F5] to run it right
there, or close the VB Editor and use Tools | Macro | Macros to run the code.
Your screen will remain unchanging while the process takes place, and a
message will appear when it has all been accomplished.

Sub OpenAndRemovePasswords()
Const masterPassword = "myPassword"
Dim filesList() As String
Dim anyFilename As Variant
Dim lCount As Integer

ReDim filesList(1 To 1) ' initialize

'get list of files to process
'will loop until [Cancel] is used
anyFilename = "kickstart" ' just to initialize the loop
Do While anyFilename < ""
anyFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If anyFilename < False Then
If filesList(UBound(filesList)) < "" Then
ReDim Preserve filesList(1 To UBound(filesList) + 1)
End If
filesList(UBound(filesList)) = anyFilename
Else ' was false, no file chosen
anyFilename = ""
End If
Loop
Application.ScreenUpdating = False
For lCount = LBound(filesList) To UBound(filesList)
If filesList(lCount) < "" Then
Workbooks.Open Filename:=filesList(lCount), _
Password:=masterPassword
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=filesList(lCount), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
MsgBox "All files have had their password removed"
End Sub


"gordon" wrote:

Hi

My work has about 500 excel files with the same password to edit the files.
I have the password and I need to open each file and save it without the
password so another application can read them. The other application can
read only excel that doesnt have passwords.

Does anyone know an efficient way to do this?

Doug

Hi

Thanks for your kind assistance.

I am sorry but I may have mislead you with my loose terminology. My files
have two passwords - one for open and one for edit - i simply need to open
each one - and remove the opening password. The edit one is not so
imporant.

And yes, they are all in the same location so a looping routine through the
code would be great.

Doug

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
gordon,
I misread your post - but not to worry! you said the workbooks have a
password required to edit them. I misread and thought it needed one to
open
the workbook.

But long story short, the code will do that also - the .Open actually
ignores the password:=masterPassword when that is done to a file without a
password required to open the file. Then the .SaveAs code clears both any
password required to open the file OR to edit it. So code should work
even
if I have actually misinterpreted your post.

However, if the protection is at a different level: the workbook or
worksheets are simply password protected, then we need to add some more
code
in between the .Open and the .SaveAs.

I suggest trying it on one file, then see if that file has been properly
altered with regards to its protection. If it hasn't, post back and tell
what process you have to use to get it unprotected as needed, such as
Tools | Protection |Unprotect | Workbook/Worksheet
and we'll be able to do that for you.

Also, if all files happen to be in one location, mention that and we can
save a lot of file choosing by hand.

"JLatham" wrote:

Gordon, the code below will allow you to browse and choose any number of
files and once you've completed that part (by clicking [Cancel] in the
file
open dialog), it will begin opening those files using the password set in
the
code and then saving them back to their original location without a
password.
They will have the same name.

Without more specifics, I can't get much more 'efficient'. If all of the
workbooks were in a single folder, the 'get filenames list' part of the
code
could do it without you having to choose them one by one, but I'm
assuming
they're scattered about in various folders.

To put the code into use: create a new workbook and then open the VB
Editor
using [Alt]+[F11]. Choose Insert | Module from the VBE menu toolbar.
Copy
and paste this code into the module, change the Const masterPassword to
contain the proper password, then you can either press [F5] to run it
right
there, or close the VB Editor and use Tools | Macro | Macros to run the
code.
Your screen will remain unchanging while the process takes place, and a
message will appear when it has all been accomplished.

Sub OpenAndRemovePasswords()
Const masterPassword = "myPassword"
Dim filesList() As String
Dim anyFilename As Variant
Dim lCount As Integer

ReDim filesList(1 To 1) ' initialize

'get list of files to process
'will loop until [Cancel] is used
anyFilename = "kickstart" ' just to initialize the loop
Do While anyFilename < ""
anyFilename = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If anyFilename < False Then
If filesList(UBound(filesList)) < "" Then
ReDim Preserve filesList(1 To UBound(filesList) + 1)
End If
filesList(UBound(filesList)) = anyFilename
Else ' was false, no file chosen
anyFilename = ""
End If
Loop
Application.ScreenUpdating = False
For lCount = LBound(filesList) To UBound(filesList)
If filesList(lCount) < "" Then
Workbooks.Open Filename:=filesList(lCount), _
Password:=masterPassword
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=filesList(lCount), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
MsgBox "All files have had their password removed"
End Sub


"gordon" wrote:

Hi

My work has about 500 excel files with the same password to edit the
files.
I have the password and I need to open each file and save it without
the
password so another application can read them. The other application
can
read only excel that doesnt have passwords.

Does anyone know an efficient way to do this?

Doug

Alright - this code will permit you to simply choose the folder that all of
the .xls files to have their open password altered and then it will work its
way through the folder directory, keeping track of all that have .xls file
type (except for itself if you happen to save the workbook with the code in
it into the same folder). Then it opens each one in turn, and saves it back
back under the original filename without a password to open it.

You MUST also provide the editing password to keep from getting that prompt
during the automatic operation. So I've added a new Const to deal with that.
Const editFilePassword = "allEdit"
change that as needed to open the files.

Later in the code I provide a way to either retain that editing password or
to remove it also (somehow I suspect you're going to want to remove it also).
There's a line of code that looks like this:
'newEditFilePassword = ""
simply remove the ' from the start of that line and the edit password will
also be removed. The line should then look like:
newEditFilePassword = ""




Sub AutoOpenAndRemovePasswords()
'change to password required to open the file
Const fileOpenPassword = "myPassword"
'you'll need to provide the editing password to open it also.
Const editFilePassword = "allEdit"
Dim newEditFilePassword As String

Dim filesList() As String
Dim anyFilename As Variant
Dim lCount As Integer
Dim basePath As String

ReDim filesList(1 To 1) ' initialize

basePath = GetFolder()
If basePath = "" Then
'user clicked [Cancel]
Exit Sub
End If
'get a 'seed' filename to kickstart the loop
anyFilename = Dir$(basePath & "*.xls")
Do While anyFilename < ""
If anyFilename < ThisWorkbook.Name Then
If filesList(UBound(filesList)) < "" Then
ReDim Preserve filesList(1 To UBound(filesList) + 1)
End If
filesList(UBound(filesList)) = basePath & anyFilename
End If
anyFilename = Dir$() ' get next possible filename
Loop
Application.ScreenUpdating = False
For lCount = LBound(filesList) To UBound(filesList)
If filesList(lCount) < "" Then
Workbooks.Open Filename:=filesList(lCount), _
Password:=fileOpenPassword, WriteResPassword:=editFilePassword
Application.DisplayAlerts = False
'
'change things in this section to determine whether or not to
'a) keep the edit password (current default)
newEditFilePassword = editFilePassword
'b) remove the edit password along with removing the open password
' to do that, remove the ' at the start of the next line of code
'newEditFilePassword = ""

ActiveWorkbook.SaveAs Filename:=filesList(lCount), _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:=newEditFilePassword, _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
MsgBox "All files have had their password removed"
End Sub
Private Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
If Len(GetFolder) 0 And Right(GetFolder, 1) <
Application.PathSeparator Then
GetFolder = GetFolder & Application.PathSeparator
End If
End Function



"gordon" wrote:

Hi

Thanks for your kind assistance.

I am sorry but I may have mislead you with my loose terminology. My files
have two passwords - one for open and one for edit - i simply need to open
each one - and remove the opening password. The edit one is not so
imporant.

And yes, they are all in the same location so a looping routine through the
code would be great.

Doug

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
gordon,
I misread your post - but not to worry! you said the workbooks have a
password required to edit them. I misread and thought it needed one to
open
the workbook.

But long story short, the code will do that also - the .Open actually
ignores the password:=masterPassword when that is done to a file without a
password required to open the file. Then the .SaveAs code clears both any
password required to open the file OR to edit it. So code should work
even
if I have actually misinterpreted your post.

However, if the protection is at a different level: the workbook or
worksheets are simply password protected, then we need to add some more
code
in between the .Open and the .SaveAs.

I suggest trying it on one file, then see if that file has been properly
altered with regards to its protection. If it hasn't, post back and tell
what process you have to use to get it unprotected as needed, such as
Tools | Protection |Unprotect | Workbook/Worksheet
and we'll be able to do that for you.

Also, if all files happen to be in one location, mention that and we can
save a lot of file choosing by hand.

"JLatham" wrote:

Gordon, the code below will allow you to browse and choose any number of
files and once you've completed that part (by clicking [Cancel] in the
file
open dialog), it will begin opening those files using the password set in
the
code and then saving them back to their original location without a
password.
They will have the same name.

Without more specifics, I can't get much more 'efficient'. If all of the
workbooks were in a single folder, the 'get filenames list' part of the
code
could do it without you having to choose them one by one, but I'm
assuming
they're scattered about in various folders.

To put the code into use: create a new workbook and then open the VB
Editor
using [Alt]+[F11]. Choose Insert | Module from the VBE menu toolbar.
Copy
and paste this code into the module, change the Const masterPassword to
contain the proper password, then you can either press [F5] to run it
right
there, or close the VB Editor and use Tools | Macro | Macros to run the
code.
Your screen will remain unchanging while the process takes place, and a
message will appear when it has all been accomplished.

Sub OpenAndRemovePasswords()
Const masterPassword = "myPassword"
Dim filesList() As String
Dim anyFilename As Variant
Dim lCount As Integer

ReDim filesList(1 To 1) ' initialize

'get list of files to process
'will loop until [Cancel] is used
anyFilename = "kickstart" ' just to initialize the loop
Do While anyFilename < ""
anyFilename = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If anyFilename < False Then
If filesList(UBound(filesList)) < "" Then
ReDim Preserve filesList(1 To UBound(filesList) + 1)
End If
filesList(UBound(filesList)) = anyFilename
Else ' was false, no file chosen
anyFilename = ""
End If
Loop
Application.ScreenUpdating = False
For lCount = LBound(filesList) To UBound(filesList)
If filesList(lCount) < "" Then
Workbooks.Open Filename:=filesList(lCount), _
Password:=masterPassword
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=filesList(lCount), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
MsgBox "All files have had their password removed"
End Sub


"gordon" wrote:

Hi

My work has about 500 excel files with the same password to edit the
files.
I have the password and I need to open each file and save it without
the
password so another application can read them. The other application
can
read only excel that doesnt have passwords.

Does anyone know an efficient way to do this?

Doug