PDA

View Full Version : Solved: check file for xls and xlsx extensions



thole
05-25-2009, 01:53 PM
I have this code, which checks for xls extensions. How do I make it so it check BOTH xls AND xlsx?


Sub File_Exists()
Dim fso
Dim file As String
Dim file2 As String
file1 = ActiveWorkbook.Name

Application.ScreenUpdating = False

For Each c In Worksheets(1).Range("F17:F5000").Cells
If c.Value <> "" Or c.Interior.ColorIndex = 3 Or c.Offset(rowoffset:=0, columnoffset:=-2).Value = "Y" Then '****

file = "C:\Documents and Settings\JP\Desktop\JP\Accent\" & c.Value & ".xls" '".xlsx" 'file = "C:\Documents and Settings\JP\Desktop\JP\Accent\" & c.Value & ".xlsx"
'file2 = "C:\Documents and Settings\JP\Desktop\JP\Accent\" & c.Value & ".xlsx"

Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
MsgBox file & " was not located.", vbInformation, "File Not Found"
c.Offset(rowoffset:=0, columnoffset:=26).Select
Selection.Value = "No Template"
c.Interior.ColorIndex = 3
Else

End If

End If

Next

MsgBox "All templates files checked."

Application.ScreenUpdating = True

End Sub

Thanks, guys!

mdmackillop
05-25-2009, 02:09 PM
How about

Dim Pth As String
Pth = "C:\Documents and Settings\JP\Desktop\JP\Accent\"
file = Pth & Dir(Pth & c.Value & ".xl*")

thole
05-25-2009, 02:13 PM
BEAUTIFUL!

thole
05-25-2009, 02:18 PM
Thank you kindly, sir!