Get filename without extension

[sharky12345]

I'm using this to loop through a folder and list all found files;

Sub GetFolder()

Range("A:L").ClearContents
Range("A1").Value = "Name"
Range("B1").Value = "ParentFolder"
Range("C1").Value = "Size (KB)"
Range("D1").Value = "DateLastModified"


Range("E1").Value = "DateCreated"
Range("F1").Value = "DateLastAccessed"




Range("G1").Value = "Type"
Range("h1").Value = "ShortName"
Range("A1").Select


Dim strPath As String
strPath = ThisWorkbook.Path


Dim OBJ As Object, Folder As Object, File As Object


Set OBJ = CreateObject("Scripting.FileSystemObject")
Set Folder = OBJ.GetFolder(strPath)


Call ListFiles(Folder)


Dim SubFolder As Object


For Each SubFolder In Folder.SubFolders
    Call ListFiles(SubFolder)
    Call GetSubFolders(SubFolder)
Next SubFolder


Range("A1").Select


End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Sub ListFiles(ByRef Folder As Object)


On Error Resume Next


For Each File In Folder.Files
        ActiveCell.Offset(1, 0).Select
        ActiveCell = File.Name
        ActiveCell.Offset(0, 1) = File.ParentFolder
        ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
        ActiveCell.Offset(0, 3) = File.DateLastModified
        ActiveCell.Offset(0, 4) = File.DateCreated
        ActiveCell.Offset(0, 5) = File.DateLastAccessed
        ActiveCell.Offset(0, 6) = File.Type
        ActiveCell.Offset(0, 7) = File.ShortName
Next File


End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Sub GetSubFolders(ByRef SubFolder As Object)


Dim FolderItem As Object


For Each FolderItem In SubFolder.SubFolders
    Call ListFiles(FolderItem)
    Call GetSubFolders(FolderItem)
Next FolderItem


End Sub

Ideally I need to find the name of each file without the file extension - is it possible?

 

[WaterGypsy]

Would this work?

ActiveCell.Offset(0, 7) = Left(File.ShortName, Len(File.ShortName) - 4)

 

[Joe4]

ActiveCell.Offset(0, 7) = Left(File.ShortName, Len(File.ShortName) - 4)

Does that assume that all files have 3 character extensions?
You may need to account for 4 character ones, like "xlsx".

 

[WaterGypsy]

DOHHHH !!!!

ActiveCell.Offset(0, 7) = Left(File.ShortName, Instr(1, File.ShortName, ".") - 1)

 

[sharky12345]

Guys,

Thanks, but it doesn't work as expected....as the following example shows;

5 More Days 'til Summer.mp3

should give me

5 More Days 'til Summer

but instead I get

5MORED~1

Any ideas?

 

[WaterGypsy]

What were you getting before using my suggestion ?

 

[shg]

ShortName is the 8.3 (DOS) filename. You want the Name property.

 

[sharky12345]

Guys - thanks to all - 'Name' does it.

 

[Jaafar Tribak]

Guys - thanks to all - 'Name' does it.

In case the file name has more than one dot in its name , I think, you should use this :

ActiveCell.Offset(0, 7) = Left(File.Name, InStrRev(File.Name, ".") - 1)