Solved: Change Password on Multiple Files in a Folder

[parttime_guy]

Hi Everybody,

I have been using this code on a single file

Sub changepass()
Dim wkbook As Workbook
Set wkbook = Application.Workbooks.Open("C:\test.xls", , , , "testpwd", True)
'True is required for editing the file
wkbook.Password = "newpwd"
wkbook.Save
wkbook.Close
End Sub

I need to to run this code on all "*.xls" files in a folder that has more than 25 files. Can this code run from the folder itself or do I always need to manually add the path.



Kindly help

Thx-n-BR

[p45cal]

You could try something like this (untested):[vba]Sub changepass()
Dim wkbook As Workbook
Dim myPath As String
Dim x
myPath = "C:\Documents and Settings\SomeOne\My Documents\" 'adjust
x = Dir(myPath & "*.xls")
Do Until x = ""
'MsgBox x
Set wkbook = Application.Workbooks.Open(x, , , , "testpwd", True)
'True is required for editing the file
wkbook.Password = "newpwd"
wkbook.Save
wkbook.Close
x = Dir
Loop
End Sub[/vba]Although I used '.xls' it also processes '.xlsm' etc. for some reason.
I didn't bother coding to browse for a folder to process, come back if you've got loads of folders to do this to. Adjust the 'mypath =' line manually.
You can run this from any file in any folder, as long as the machine can see mypath;the file doesn't need to be saved in mypath.

[parttime_guy]

Hi P4Cal,

Thx for the quick reply - I tested the code
I am getting an error on the following line.

Set wkbook = Application.Workbooks.Open(x, , , , "testpwd", True)

Iam using Excel 07.

Can u help? - I have also posted something today in another Post regarding the above code - something similar which use to run perfectly in Excel 03 and does not seem to run in 07.

Plz guide.....

Thx-n-BR

[p45cal]

I've tested in 07 and it works fine.
Hints:
What is the error message?
Post your code.

[parttime_guy]

Could there be a problem as Iam trying to use a network drive or is the code crashing because Iam trying to use it on 450+ files simultaneously.

As in my earlier post I had reffered to a solution done in Excel 03 - kindly view the following post

http://www.vbaexpress.com/forum/showthread.php?t=28020

Thx-n-BR

[p45cal]

Could there be a problem as Iam trying to use a network drive or is the code crashing because Iam trying to use it on 450+ files simultaneously.

As in my earlier post I had reffered to a solution done in Excel 03 - kindly view the following post

http://www.vbaexpress.com/forum/showthread.php?t=28020

Thx-n-BR

As GTO said: "You may wish to attach a new example wb with the code thats working in '03, but collapsing in the later ver."

as I said..
"What is the error message?
Post your code."

I can't advise, because I can't see over your shoulder.

[parttime_guy]

Luckly – I found a machine in my office that had Excel 03 – I ran the code that GTO had provided earlier (without changes), it worked perfectly - the files were on a network drive. This code changes about 450+ files currently – n all these files have the same password.

I still gotta find out what’s the problem in running the code on Excel 07. Strange.

Thx-n-BR – catch u guz later on this one.

[geekgirlau]

Don't forget to mark this thread as "solved" - under "Thread Tools" at the top of the page.