File name without extension name VBA

Question

I need to get file name without extension name by VBA. I know ActiveWorkbook.Name property , but if user haves Windows property Hide extensions for known file types turn off, the result of my code will be [Name.Extension]. How can I return only name of Workbook independent of windows property?

I try even ActiveWorkbook.Application.Caption but I can't customize this property.

Softball

Answer 1

strTestString = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))

full credit: http://mariaevert.dk/vba/?p=162

Commutable

This doesn't work if there is no file extension but there is a dot in the name. – Kershaw

No? Can you tell me what error is occuring? I am missing it. – Commutable

Think it through. You're using an InStrRev to find the dot. Well, what if the file name is "John.And.Mary.Spreadsheet", because they have Hide Extensions option on? Now it thinks the file is "John.And.Mary" and the file extension is "Spreadsheet". – Kershaw

Not according to my system. Not sure if the version you are using changes things, but the .Name property returns the full name, regardless of the Hidden extensions. At least, that is what it is doing for me. – Commutable

Read the OP's question. He's saying he gets the extension only if that option is turned off. – Kershaw

Is it possible the OP is wrong, hence asking for help on SO? Or are all OPs infallible? – Commutable

the Hide known extensions extensions is just a setting in file explorer it doesn't have an effect in VBA. Having said that a file doesn't have to have any extension so that may be what is happening here – Autopsy

I saved my test file with no extension, then manually opened it in Excel and produced the same results. Wondering if Windows 'assigns' an extension when you manually select a program to open the file? – Commutable

Hiding the Extension doesn't mean that the file doesn't have one, it means it isn't being shown by the file explorer – Steroid

Answer 2

The answers given here already may work in limited situations, but are certainly not the best way to go about it. Don't reinvent the wheel. The File System Object in the Microsoft Scripting Runtime library already has a method to do exactly this. It's called GetBaseName. It handles periods in the file name as is.

Public Sub Test()

    Dim fso As New Scripting.FileSystemObject
    Debug.Print fso.GetBaseName(ActiveWorkbook.Name)

End Sub

Public Sub Test2()

    Dim fso As New Scripting.FileSystemObject
    Debug.Print fso.GetBaseName("MyFile.something.txt")

End Sub

Instructions for adding a reference to the Scripting Library

Eucaine

Didn't know that was there! Nice answer. – Commutable

RbeerDuck, When I run this command, there is a compile error "User-defined type not defined" – Youthen

@Youthen you need to add a reference to the library. – Eucaine

Can you explain that please? – Youthen

and if "Hide extensions for known file types" is on, how will GetBasename know that "John.and.Mary.spreadsheet" is already the base name ? I think it will assume "spreadsheet" is the file extension and will return "John.and.Mary", so this doesn't address OP's problem. – Steelwork

@Steelwork I think you should try it before you suggest it doesn't work. – Eucaine

It certainly doesn't work if you use the Workbook.Name property as the argument for these fso functions when "Hide extensions for known file types" is set. Workbook.Fullname should be used instead. – Magner

Sorry - should've explained myself. It won't work if you have extensions hidden in explorer and the filename is something stupid like "Myworkbook.csv.xlsx" (far from uncommon). In this case the .Name property is "Myworkbook.csv" and GetBaseName returns "Myworkbook" which is incorrect. Furthermore: if you try to use the companion fso function GetExtensionName with Workbook.Name property when extensions are hidden you won't get an answer. Far better practice to just use the Workbook.Fullname property, which returns the same string regardless of Explorer's "hide extensions" setting. – Magner

I prefer late binding - Dim fso: Set fso = CreateObject("Scripting.FileSystemObject") – Floranceflore

Answer 3

Simple but works well for me

FileName = ActiveWorkbook.Name 
If InStr(FileName, ".") > 0 Then 
   FileName = Left(FileName, InStr(FileName, ".") - 1) 
End If

Broussard

This will fail if you have more dots in the file name. – Floranceflore

+1 I found this answer helpful. Tip: use ".x" or even ".xls" in case you have more dots in the file name. – Patras

If you have more dots in the file name use InStrRev in place of both InStr 's – Bibliofilm

This will also fail assuming the Windows property Hide extensions for known file types is set to true and .Name returns the file name without extension. Therefore it wouldn't be independent Windows property as requested by the OP. – Manoff

Answer 4

Using the Split function seems more elegant than InStr and Left, in my opinion. (Edit 6/2/2023) For files that contain "." included in the base name, you will want to drop the last element of an array.

Private Sub CommandButton1_Click()
Dim ThisFileName As String
Dim BaseFileName As String

Dim FileNameArray() As String
Dim FileNameArrayLen As Integer
   
ThisFileName = ThisWorkbook.Name

FileNameArray = Split(ThisFileName, ".")
FileNameArrayLen = UBound(FileNameArray)

ReDim Preserve FileNameArray(0 To FileNameArrayLen - 1) As String

BaseFileName = Join(FileNameArray, ".")

MsgBox "This file name is " & ThisFileName & "." & Chr(13) _
      & "Base file name is " & BaseFileName

End Sub

Profluent

What if the filename is My.Special.Workbook.xlsm? Split returns a four-element array. – Milinda

Answer 5

You could always use Replace() since you're performing this on the workbook's Name, which will almost certainly end with .xlsm by virtue of using VBA.

Using ActiveWorkbook per your example:

Replace(Application.ActiveWorkbook.Name, ".xlsm", "")

Using ThisWorkbook:

Replace(Application.ThisWorkbook.Name, ".xlsm", "")

Alienee

Answer 6

This thread has been very helpful to me lately. Just to extend on the answer by @RubberDuck, the File System Object in the Microsoft Scripting Runtime library is already there to achieve this. Also if you define it as an Object as below, it will save you the hassle of having to enable 'Microsoft Scripting Runtime' in VBA Tools > References:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Debug.Print fso.GetBaseName(ActiveWorkbook.Name)

In this way it will return name of the ActiveWorkbook without extension.

There is another way by using INSTRREV function as below:

Dim fname As String
fname = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
MsgBox fname

Both will return the same result. Also in both of the methods above, they will retain any full-stops in the file name and only get rid of the last full-stop and the file extension.

Alexandraalexandre

Answer 7

To be verbose it the removal of extension is demonstrated for workbooks.. which now have a variety of extensions . . a new unsaved Book1 has no ext . works the same for files

Function WorkbookIsOpen(FWNa$, Optional AnyExt As Boolean = False) As Boolean

Dim wWB As Workbook, WBNa$, PD%
FWNa = Trim(FWNa)
If FWNa <> "" Then
    For Each wWB In Workbooks
        WBNa = wWB.Name
        If AnyExt Then
            PD = InStr(WBNa, ".")
            If PD > 0 Then WBNa = Left(WBNa, PD - 1)
            PD = InStr(FWNa, ".")
            If PD > 0 Then FWNa = Left(FWNa, PD - 1)
            '
            ' the alternative of using split..  see commented out  below
            ' looks neater but takes a bit longer then the pair of instr and left
            ' VBA does about 800,000  of these small splits/sec
            ' and about 20,000,000  Instr Lefts per sec
            ' of course if not checking for other extensions they do not matter
            ' and to any reasonable program
            ' THIS DISCUSSIONOF TIME TAKEN DOES NOT MATTER
            ' IN doing about doing 2000 of this routine per sec

            ' WBNa = Split(WBNa, ".")(0)
            'FWNa = Split(FWNa, ".")(0)
        End If

        If WBNa = FWNa Then
            WorkbookIsOpen = True
            Exit Function
        End If
    Next wWB
End If

End Function

Cetus

Answer 8

This gets the file type as from the last character (so avoids the problem with dots in file names)

Function getFileType(fn As String) As String

''get last instance of "." (full stop) in a filename then returns the part of the filename starting at that dot to the end
Dim strIndex As Integer
Dim x As Integer
Dim myChar As String

strIndex = Len(fn)
For x = 1 To Len(fn)

    myChar = Mid(fn, strIndex, 1)

    If myChar = "." Then
        Exit For
    End If

    strIndex = strIndex - 1

Next x

getFileType = UCase(Mid(fn, strIndex, Len(fn) - x + 1))

End Function

Luthanen

Answer 9

I use a macro from my personal.xlsb and run it on both xlsm and xlsx files so a variation on David Metcalfe's answer that I use is

Dim Wrkbook As String

Wrkbook = Replace(Application.ActiveWorkbook.Name, ".xlsx", ".pdf")

Wrkbook = Replace(Application.ActiveWorkbook.Name, ".xlsm", ".pdf")

Jointless

Answer 10

Here is a solution if you do not want to use FSO. There were some similar answers before, but here some checks are done to handle multiple dots in name and name without extension.

Function getFileNameWithoutExtension(FullFileName As String)

    Dim a() As String
    Dim ext_len As Integer, name_len As Integer


    If InStr(FullFileName, ".") = 0 Then
       getFileNameWithoutExtension = FullFileName
       Exit Function
    End If
    
    a = Split(ActiveWorkbook.Name, ".")
    ext_len = Len(a(UBound(a))) 'extension length (last element of array)
    name_len = Len(FullFileName) - ext_len - 1 'length of name without extension and a dot before it
    getFileNameWithoutExtension = Left(FullFileName, name_len)
    
End Function

Sub test1() 'testing the function
 MsgBox (getFileNameWithoutExtension("test.xls.xlsx")) ' -> test.xls
 MsgBox (getFileNameWithoutExtension("test")) ' -> test
 MsgBox (getFileNameWithoutExtension("test.xlsx")) ' -> test
End Sub

Medin

Answer 11

No FSO and just a few lines- wanted q quick clean way to do this- I don't like the on error resume next but this should work for all cases above.

Function GetBaseName(strFN As String, Optional MaxLen As Integer = 4) As String
    Dim Tmp As Variant                          ''variant to hold split array & Last value
        On Error Resume Next                    ''if something errors out let it fall to next line
        Tmp = Split(strFN, ".")                 ''Break at '.'
        Tmp = Tmp(UBound(Tmp))                  ''take last element- if no '.' found returns whole line
        If Tmp > "" And Len(Tmp) <> Len(strFN) And Len(Tmp) <= MaxLen Then ''Last element isn't null and is less than mac length
            GetBaseName = Left(strFN, Len(strFN) - Len(Tmp) - 1) ''remove tmp and dot
        Else
            GetBaseName = strFN                 ''Otherwise return full name
        End If
        On Error GoTo 0                         ''Resume normal error checking
End Function

Veolaver