VBA: USE InStrRev to get file Name

[DeanP]

I am trying to get the filenames from a folder and place it in a textbox. I am using File Dialog Open to
get the file path\filenames.
Someone kindly helped me to get the directory using InStrRev:

n = InStrRev(fDialog.SelectedItems(1), "\")
 TextBox2.Value = Left(fDialog.SelectedItems(1), n)

I want the filename instead, so I found

varFileName = Mid(fDialog.SelectedItems(1), InStrRev(fDialog.SelectedItems(1), "\") + 1, Len(fDialog.SelectedItems(1)))

I don't know how to put all of this together to get the file name into TextBox2.

Any help would be appreciated.

[Paul_Hossler]

These are the pieces using VBA

Option Explicit
Sub GetFileName()
    Dim s As String, s1 As String
    Dim i As Long
    
    s = "C:\Users\Me\Documents\Latest File.xlsx"
    
    i = InStrRev(s, "\")
    
    s1 = Right(s, Len(s) - i)
    MsgBox s1
    
End Sub

or using the FileSystemObject

Sub GetFileName2()
    Dim s As String, s1 As String
    
    s = "C:\Users\Me\Documents\Latest File.xlsx"
    
    s1 = CreateObject("Scripting.FileSystemObject").GetFileName(s)
    MsgBox s1
    
End Sub

[DeanP]

Thank you for this. How would this code change if there is more than 1 file in the folder (which may be the case).? Ideally the macro would get the names of all files.

[Kenneth Hobs]

Sub DirStdOut()  
  Dim s As String, a() As String
  ' /b = bare file listing, /s = search subfolders, /c = open command shell, run command and then close shell.
  ' /a:-d means list files only and not subfolders
  s = CreateObject("Wscript.Shell").Exec("cmd /c dir C:\myfiles\wp\Klaus\*.* /a:-d /b").StdOut.ReadAll
  a() = Split(s, vbCrLf)
  With Range("A1")
    .EntireColumn.Clear
    .Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
  End With
End Sub