Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|