Using dir() to select .xls but NOT .xlsx files

Cindy Ellis · Jul 20, 2009

Using Excel 2007:
I am trying to use dir() in a While...wend to get a specific list of files to process, but I want the list to only include those with a .xls extension, not those ending in .xlsx. It seems that the file filter ".xls" doesn't exclude those files with a ".xlsx" extension.
Sample code demonstrating the problem (will show the problem if there is at least one cycle that meets a*.xls and one that meets a*.xlsx).
Is there a workaround to this at the dir() level? Or do I need to go ahead and open the file and test its contents?

Code:

Sub TestDir()
    On Error GoTo Nofile   'trap cancel, X-out of the dialog, etc.
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .InitialFileName = "a*.xls"
        .Show
        GetDir = .SelectedItems(1)
    End With
    On Error GoTo 0       'reset error handler
    
    If GetDir <> "False" Then
        Path = CurDir & "\"
    Else
        MsgBox "Directory not selected"
        Exit Sub
    End If
    
    df = Dir(Path & "a*.xls") 'should get the list of a*.xls files in the directory, but gets a*.xlsx too
    While df <> ""
        MsgBox (df)
    df = Dir()
    Wend
    GoTo OK     'skip the error message
    
Nofile:
    MsgBox ("No file selected.  Macro will exit.")
    Exit Sub
    
OK:

End Sub

Thanks for any help,
Cindy

Blade Hunter · Jul 21, 2009

You could do something like:

change

Code:

    While df <> ""
        MsgBox (df)
    df = Dir()

to

Code:

    While df <> ""
        if not upper(right(df, 1)) = "X" then MsgBox (df)
    df = Dir()

I free typed that so you may need to double check my syntax

Cheers

Dan

Cindy Ellis · Jul 21, 2009

Thanks! I think I was so close to it I wasn't thinking clearly.
Your syntax wasn't perfect, ("upper" needed to be changed to "ucase") but close enough to get me to where I was trying to go. For the benefit of other folks who might happen across this thread, here it is put together (of course I'm trying to do something more than show files in a message box)

Code:

Sub GetXLSfiles()
    Dim df As String, Path As String, GetDir As String
    
    On Error GoTo Nofile   'trap cancel, X-out of the dialog, etc.
    With Application.FileDialog(msoFileDialogOpen)  'displays the file open dialog with file filter applied
        .AllowMultiSelect = False
        .InitialFileName = "a*.xls"
        .Show
        GetDir = .SelectedItems(1)
    End With
    On Error GoTo 0       'reset error handler
    
    If GetDir <> "False" Then
        Path = CurDir & "\"
    Else
        MsgBox "Directory not selected"
        Exit Sub
    End If
    
    df = Dir(Path & "a*.xls") 'gets the list of *.xls and *.xlsx files
    While df <> ""
        If Not UCase(Right(df, 1)) = "X" Then  'only proces the xls files, not xlsx
        
            MsgBox (df)  'other useful code here
        
        End If
    df = Dir()
    Wend
    GoTo OK     'skip the error message
    
Nofile:
    MsgBox ("No file selected.  Macro will exit.")
    Exit Sub
    
OK:

End Sub

Thanks again!

Richard Schollar · Jul 21, 2009

That seems very odd that it picks up both xlsx and xls. I wonder why that occurs?

Cindy Ellis · Jul 21, 2009

That's why I posted the question...it just didn't make sense. I thought I was doing something wrong with the qualifier in the dir function.
What's really odd is that "*.x" and "*.xl" don't select anything, but as soon I make it "*.xls" (with or without something to the left of the asterisk), it pulls up both xls and xlsx files. It mostly means I have to pay more attention. I thought it would be convenient to use the extension to distinguish files that had been processed from those that hadn't been processed. Oh well...
(and Hi Richard...I haven't posted in ages...good to see the familiar names are still here).

Cindy

Richard Schollar · Jul 21, 2009

It does the same with .html and .htm files too - I wonder if it's something to do with Dir only being able to resolve 3 character extensions? I'll call in some others to take a look...

Hi Cindy

Good to see you back!

RoryA · Jul 21, 2009

At a guess, I would assume it relates back to the old 8.3 filename structure. It was probably updated to cope with longer file names but they didn't bother changing the extension part. Hopefully Colin will pop by - he's better at this sort of esoterica!

Colin Legg · Jul 21, 2009

I wasn't aware of this either but I agree with the observation that it's only checking the first three characters of the extension.

Googling threw up this old thread where some surprised Access MVPs came to the same conclusion:
http://www.pcreview.co.uk/forums/thread-1628303.php

I don't have a definitive answer as to *why* it's happening (Rory could well be right

), but I know some people who might be able to offer some more information so I will ask them and update this thread if I get a good answer!

Richard Schollar · Jul 21, 2009

I guess before the introduction of the new(er) 2007+ format files, it probably wasn't something that would come up all that often.

Cindy Ellis · Jul 21, 2009

And before the introduction of 2007, I was carefully choosing the names and locations of the files I generated so that they wouldn't be picked up by the dir() function. I got lazy, thinking the extension would differentiate...I guess it serves me right

Anyway, using the general approach above, just to make it generally more robust (so it won't pull in xlsb files either), I changed

Code:

If Not UCase(Right(df, 1)) = "X" Then

to

Code:

If UCase(Right(df, 3)) = "XLS" Then

Thanks again to everyone.

Cindy

Using dir() to select .xls but NOT .xlsx files

Cindy Ellis

MrExcel MVP

Excel Facts

Blade Hunter

Well-known Member

Cindy Ellis

MrExcel MVP

Richard Schollar

MrExcel MVP

Cindy Ellis

MrExcel MVP

Richard Schollar

MrExcel MVP

RoryA

MrExcel MVP, Moderator

Colin Legg

MrExcel MVP

Richard Schollar

MrExcel MVP

Cindy Ellis

MrExcel MVP

Similar threads

Forum statistics

Share this page

Using dir() to select .xls but NOT .xlsx files

MrExcel MVP

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock