Help in Getting the File Name from a path

[Tom]

I have created the following macros and function to import a file and then
extract just the path name. Problem is that the function returns the entire
path name and not just the file mane. The importation of the file works fine.
Below is the code I wrote, any suggestions for a fix?

Dim myFileName As Variant

Sub ImportRunData()
ImportDisplayRDFile
stFullName = myFileName
GetFileName (stFullName)
End Sub
----------------------------------------------
Sub ImportDisplayRDFile()
'
'ImportRunDataFile Macro
'
myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv")
If myFileName = False Then
Exit Sub 'user hit cancle
End If
Sheets("Sheet1").Select
With ActiveSheet
With .QueryTables.Add(Connection:="Text;" & myFileName,
Destination:=.Range("$AG$1"))
.Name = "Pick Place for JRB001078B DDU"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("E3").Select
End With
End Sub
----------------------------------------
Function GetFileName(stFullName As String) As String
'GetFileName returns the file name from the end of a full path namme
'stFullName is returned if no path seperator is found
Dim stPathSep As String 'Path Separator Character
Dim iFNLength As Integer 'Length of stFullName
Dim i As Integer
stPathSep = Application.PathSeparator
iFNLength = Len(stFullName)
stFullName = myFileName
'Find last path separator character, if there is one
For i = iFNLength To 1 Step -1
If Mid(stFullName, i, 1) = stPathSep Then Exit For
Next i
GetFileName = Right(stFullName, iFNLength - 1)
Range("C5") = GetFileName
End Function
-----------------------------------------------------------------

 

[Bob Phillips]

Function GetFileName(stFullName As String) As String
GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName,
Application.PathSeparator))
End Function

 

[Jacob Skaria]

Function GetFileName(strFullName As String) As String
GetFileName = Mid(strFullName, InStrRev(strFullName,
Application.PathSeparator) + 1)
End Function

 

[Tom]

Bob,

Thank you for the help, it worked just fine.