CODEWITHSUNDEEP
excelvba
0m3r
0m3r

Reputation: 12499

How to list files names in folder

I'm trying to create a list of file names in folder for reference, The following code is listing all the file names with extension Filename.pdf

how do I exclude the extension from file name? .pdf

Option Explicit
Sub GetFileName()
    Dim xlRow As Long
    Dim sDir As String
    Dim FileName As String
    Dim sFolder As String

    sFolder = "C:\Temp\"

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder"
        .InitialFileName = sFolder
        .Show
        If .SelectedItems.Count <> 0 Then
            sDir = .SelectedItems(1) & "\"
            FileName = Dir(sDir, 7)

            Do While FileName <> ""
                Range("A1").Offset(xlRow) = FileName
                xlRow = xlRow + 1
                FileName = Dir
            Loop
        End If
    End With
End Sub

Upvotes: 0

Views: 1223

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If the filename itself does not contain a period, you can use Split():

Option Explicit
Sub GetFileName()
    Dim xlRow As Long
    Dim sDir As String
    Dim FileName As String
    Dim sFolder As String

    sFolder = "C:\Temp\"

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder"
        .InitialFileName = sFolder
        .Show
        If .SelectedItems.Count <> 0 Then
            sDir = .SelectedItems(1) & "\"
            FileName = Dir(sDir, 7)

            Do While FileName <> ""
                Range("A1").Offset(xlRow) = Split(FileName, ".")(0)
                xlRow = xlRow + 1
                FileName = Dir
            Loop
        End If
    End With
End Sub

Upvotes: 1

John Coleman
John Coleman

Reputation: 51998

I'm not 100% sure what you are asking, but I think that

If FileName Like "*.pdf" Then
    Range("A1").Offset(xlRow) = Mid(FileName,1,Len(FileName)-4)
End If

might be what you are after.

Upvotes: 2

Related Questions