How to remove file extension from file name (VBA)

Question

I have a filename variable that contains : "Filename.csv" . To extract the filename from a path I use: Filename=Dir([fStr]) where fStr is retrieved from the file that I selected.

I only need the filename without ".csv". How do I remove the ".csv" extension?

Answer 1

You can use the replace function:

Filename = replace(Dir([fStr]),".csv","")

Answer 2

My code runs on various systems which may not allow scripting. I rewrote this to get around this limitation.

Function FileGetBaseNameNoExt(aFilenameStr As String) As String
  Dim TmpCnt As Integer
  Dim TmpStr As String

  FileGetBaseNameNoExt = aFilenameStr

  If InStr(aFilenameStr, ".") = False Then
    Exit Function
  End If

  TmpCnt = 1
  TmpStr = Left(Right(aFilenameStr, TmpCnt), 1)
  While TmpStr <> "."
    TmpCnt = TmpCnt + 1
    TmpStr = Left(Right(aFilenameStr, TmpCnt), 1)
  Wend

  'Make Sure the Filename is Not Something Odd like .csv
  If TmpCnt < Len(aFilenameStr) Then
    FileGetBaseNameNoExt = Left(aFilenameStr, Len(aFilenameStr) - TmpCnt)
  End If
End Function

Answer 3

It's best to use a function like GetBaseName() instead of relying on functions to replace text. Windows allows periods to appear within the base filename so something like this is legitimate:

My .csv for Bob.csv

Using Replace() would result in:

My  for Bob

Not what you're looking for. A better approach would be:

Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(fStr)