DIR
 

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

DIR
 

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 W Smith" wrote in message
...
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

DIR
 

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.


"Paul W Smith" wrote:

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


.

DIR
 

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

--


Gary Keramidas
Excel 2003


"Paul W Smith" wrote in message
...
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

DIR
 

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

Mike - I was amazed too that it happened!

Thanks all.

"Paul W Smith" wrote in message
...
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