How to get Path and File Name from a Full Path

1. With use InStrRev Function

1 2 3 4 5

Sub Sample1() Dim PathName As String, FileName As String, pos As Long pos = InStrRev("D:\ExcelPaidjo\File\FileName\filename.xlsm", "\") MsgBox pos End Sub

Then, to get Path and File Name we use, Left and Mid function.

1 2 3 4 5 6 7

Sub Sample2() Dim PathName As String, FileName As String, pos As Long pos = InStrRev("D:\ExcelPaidjo\File\FileName\filename.xlsm", "\") PathName = Left("D:\ExcelPaidjo\File\FileName\filename.xlsm", pos) FileName = Mid("D:\ExcelPaidjo\File\FileName\filename.xlsm", pos + 1) MsgBox PathName & vbCrLf & FileName End Sub

2. With Use Dir Function

1 2 3 4 5

Sub Sample3() Dim PathName As String, FileName As String FileName = Dir("D:\ExcelPaidjo\File\FileName\filename.xlsm") MsgBox FileName End Sub

then, to get path name we use Replace function.

1 2 3 4 5 6

Sub Sample3() Dim PathName As String, FileName As String FileName = Dir("D:\ExcelPaidjo\File\FileName\filename.xlsm") PathName = Replace("D:\ExcelPaidjo\File\FileName\filename.xlsm", FileName, "") MsgBox PathName & vbCrLf & FileName End Sub

Download File