PDA

View Full Version : [SOLVED] VBA: USE InStrRev to get file Name



DeanP
12-20-2018, 10:05 AM
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
12-20-2018, 10:40 AM
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
12-20-2018, 11:11 AM
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
12-20-2018, 12:13 PM
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