DIR

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
 
O

Otto Moehrbach

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
 
M

Mike H

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.
 
G

Gary Keramidas

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
 
P

Paul W Smith

Many thanks to the three of you for all producing different but workable
solutions.

Mike - I was amazed too that it happened!

Thanks all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top