DIR

  • Thread starter Thread starter Paul W Smith
  • Start date Start date
P

Paul W Smith

I want to produce a listing of all the workbooks in a folder what have the
file extension .xls.

When I use Dir(*.xls), I also get all the .xlsm and .xlsb files, which is
not what I want.

Is there a way around this issue?

Paul Smith
 
Paul
You didn't post your code, so I'll assume your code sets the file name to
the variable TheFile. You could use an IF statement like:
If Right(TheFile,1)<>"m" And Right(TheFile,1)<>"b" then
'Your code
End If
HTH Otto
 
Paul,

I have to confess that on reading your post I thought 'No Way will it do
that' but surprisingly; to me at least, searching for .xls does return .xlsm
files. I don't understand why but here's a workaround which tests the length
of the file extension and ensures that including the . it is 4 characters
long.

Sub LoopThroughDirectory()
x = 1
'Change this to your directory
MyPath = "C:\"
activefile = Dir(MyPath & "*.xls")
Do While activefile <> ""
If Len(Mid(activefile, InStr(activefile, "."))) = 4 Then
Cells(x, 1) = activefile
x = x + 1
End If
activefile = Dir()
Loop
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
which version of excel? this may work in 2003, but not 2010. just change the
lookin path.

Sub test()
Set fs = CreateObject("Scripting.FileSystemObject")
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

Set fs = Application.FileSearch
With fs
.LookIn = "YourPath"
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
ws.Range("A" & i).Value = .FoundFiles(i)
Next i
End With
End Sub
 
Many thanks to the three of you for all producing different but workable
solutions.

Mike - I was amazed too that it happened!

Thanks all.
 
Back
Top