PDA

View Full Version : Solved: Make Code run only on xls files



parttime_guy
03-29-2010, 10:49 PM
I have this sub below.....
When I run this it runs on all files in the folder (including .zip files)
Can I run this sub only on ".xls" files


Sub Remove_password()

Dim wkb1 As Workbook
Dim wksMACRO As Worksheet
Dim fso, f, fs, f1
Dim FileExtStr As String
Dim Filter As String
'------Password's need to be entered in Sheet1
Set Pass1 = Sheet1.Range("B1")
Set Pass2 = Sheet1.Range("B2")

Application.DisplayAlerts = False


ThisWorkbook.Activate

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.getfolder(ThisWorkbook.path)
Set fs = f.Files


For Each f1 In fs
If ThisWorkbook.Name <> f1.Name Then
Set wkb1 = Application.Workbooks.Open(ThisWorkbook.path & "\" & f1.Name, , , , Pass1, True)

wkb1.Password = Pass2
wkb1.Save
wkb1.Close



End If
Next

MsgBox "Congratulations!!!" & vbCrLf & "All Files in the path have been updated successfully"

Application.DisplayAlerts = True

End Sub



Kindly help

Thx-n-BR

mdmackillop
03-30-2010, 12:44 AM
For Each f1 In fs
If Right(f1.Name,3) = "xls" Then
If ThisWorkbook.Name <> f1.Name Then

parttime_guy
03-30-2010, 02:53 AM
Hi MD,

Thx for the tip - I used your lines in the sub & the code is working fine.

I had some time to figure out there was an "End If" missing - but everything is sorted out.

Thanks for your help. :bow:

Best regards

mdmackillop
03-30-2010, 07:26 AM
Try Smart Indent (see my signature) and always use Option Explicit. These make spotting errors much easier

parttime_guy
03-30-2010, 08:20 AM
Thx MD for that suggestion, I have downloaded the tool "Smart Indent" Tool.

BR