How do I use a macro to put a password on an exel file with excel 2010

[Skeve3]

Hi All .

I previously used the following macro to put a password on all excel files within the specified network directory. Now that I have moved over to excel 2010 this macro no longer works on either xls or xlsm files. Any help would be appreciated.

I previously use the following macro to put a password on all files within the specified network directory. Now that I have moved over to excel 2010 this macro no longer works on either xls or xlsm files. Any help would be appreciated.</SPAN>


Sub OpenAndProcess()</SPAN></SPAN>
Dim vaFileName As Variant</SPAN></SPAN>
Const MyDir As String = "W:\network folder path"</SPAN></SPAN></SPAN>
Const strPwd As String = "****password****</SPAN>"</SPAN></SPAN>

With Application.FileSearch</SPAN></SPAN>
.NewSearch</SPAN></SPAN>
.LookIn = MyDir</SPAN></SPAN>
.SearchSubFolders = False</SPAN></SPAN>
.FileType = msoFileTypeExcelWorkbooks</SPAN></SPAN>
If .Execute > 0 Then</SPAN></SPAN>
Application.ScreenUpdating = False</SPAN></SPAN>
For Each vaFileName In .FoundFiles</SPAN></SPAN>
ProcessData vaFileName, strPwd</SPAN></SPAN>
Next</SPAN></SPAN>
Else</SPAN></SPAN>
MsgBox "There were no Excel files found."</SPAN></SPAN>
End If</SPAN></SPAN>
Application.ScreenUpdating = True</SPAN></SPAN>
End With</SPAN></SPAN>

End Sub</SPAN></SPAN>

Sub ProcessData(ByVal Fname As String, Pwd As String)</SPAN></SPAN>
Dim wbk As Workbook</SPAN></SPAN>

Set wbk = Workbooks.Open(Filename:=Fname)</SPAN></SPAN>

With wbk</SPAN></SPAN>
Application.DisplayAlerts = False</SPAN></SPAN>
.SaveAs Filename:=Fname, Password:=Pwd</SPAN></SPAN>
Application.DisplayAlerts = True</SPAN></SPAN>
.Close</SPAN></SPAN>
End With</SPAN></SPAN>

End Sub</SPAN></SPAN>

 

[Trevor G]

Welcome to the Forum, FileSearch was removed in 2007 so you could you use DIR command instead. There are plenty of threads on this. Here is a link to watch several methods from youtube. https://www.google.co.uk/#q=using+Excel+VBA+DIR+Command+to+open+all+workbooks+in+a+folder+youtube

 

[mkzero]

Still not able to get this to work with excel 2010 and .XLSX files anyone able to post an updated code?