Extracting Filename from Dir()

Question

I have a macro that uses the Dir function.

MyFile = Dir(CurDir() & Sep & "*.xls")
Do While MyFile <> ""
     ...
     MyFile = Dir()
Loop

How do I extract just the filename from MyFile (exclude the extension)?

Answer 1

It doesn't look like there is a convenient built-in function, so you'll have to do some string manipulation:

' Get just the file name and extension
lastPathIndex = InStrRev(MyFile, Application.PathSeparator)
If lastPathIndex >= 1 Then
    MyFile = Right(MyFile, Len(MyFile) - lastPathIndex)
End If

' Now get the file name without the extension  
lastDotIndex = InStrRev(MyFile, ".")
If lastDotIndex >= 1 Then
    MyFile = Left(MyFile, lastDotIndex - 1)
End If

' MyFile now contains just the filename

Answer 2

The MyFile variable holds the file name with an extension and without the path.

Based on your input, you know the extension for any file found will be .xls , so you can use update your code with a single line

MyFile = Dir(CurDir() & Sep & "*.xls")
Do While MyFile <> ""
     MyFile = Left(MyFile, Len(MyFile) - 4)
     ...
     MyFile = Dir()
Loop

Answer 3

Another method is

fileName = Split(MyFile, Sep)(UBound(Split(MyFile, Sep)))