How to make the Dir() function case insensitive

Posted on 2006-05-16
Last Modified: 2010-05-01
Hi there

My application needs to get all files with the extension .DAT in a particular directory and at the moment the
call to DIr looks like this:

gstrDATFiles = gstrDirPath & "*.DAT"
'Now process all files
strFileName = Dir(gstrDATFiles, vbNormal)

and it does the job bt requires that all filenames be in upper case.  What I want is to get all
files ending .Dat or .DAT or .dat or even .dAt  so that I am not restricted by case.

Anyone any ideas.

Many thanks
Question by:Samm1502
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    dim fso as object
    set fso = createobject("scripting.filesystemobject")

    dim folder as object
    set folder = fso.getfolder(gstrDatfiles)

    dim file as object
    for each file in folder.files
      if ucase( like "*.DAT" then
      end if
    LVL 26

    Expert Comment

    LVL 76

    Expert Comment

    The Dir$() or Dir() function is not case sensitive, so you should have no problem. Can you suggest that you check again?
    LVL 6

    Accepted Solution

    Hi Sam, Dir is not case sensitive but it will ONLY return 1 file name.

    So, to use FSO is a good idea for this purpose.

    LVL 76

    Expert Comment

    You are probably already stepping through a loop, but just in case you are not, this is what it should look like.

    Sub WalkThroughFolder()
        Dim strFileName As String
        gstrDATFiles = gstrDirPath & "*.DAT"
        strFileName = Dir$(gstrDATFiles, vbNormal)
        Do Until strFileName = ""
            'process file  gstrDirPath & strFileName
            strFileName = Dir$()
    End Sub
    LVL 9

    Expert Comment

    by:Naveen Swamy
    dir is case insensitive

    Private Sub Form_Load()
        Dim sTemp As String
        sTemp = Dir(sTemp, vbNormal)
        While sTemp <> ""
            Debug.Print sTemp
            sTemp = Dir()
    End Sub

    Author Comment

    Many thanks - sorry for delay

    This was handed to me as a problem but in fact it does process fileswith names of all cases.

    LVL 76

    Expert Comment

    You're welcome.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now