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 W Smith" <(E-Mail Removed)> wrote in message
news:OJ$(E-Mail Removed)...
> 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,

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

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" <(E-Mail Removed)> wrote in message
news:OJ$(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
news:OJ$(E-Mail Removed)...
>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
>
>