Extract Filename without Path and Extension

[BenChod]

Hello All -

I am trying to copy the name of my workbooks that are copied to a master worksheet. I want to include the name of the workbook for tracking purposes. For some reason, the entire path is being copied over instead of the workbook name. Hoping someone can take a quick look and tell me what I am doing wrong. Here is the entire code:


Sub GetData_Example5()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, destrange As Range
Dim sh As Worksheet
SaveDriveDir = CurDir
MyPath = "C:\Data\Testing\War Room" 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*", _
MultiSelect:=True)
If IsArray(FName) Then
' Sort the Array
FName = Array_Sort(FName)
Application.ScreenUpdating = False
'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = Format(Now, "dd-mm-yy h-mm-ss")
'Loop through all files you select in the GetOpenFilename dialog
For N = LBound(FName) To UBound(FName)
'Find the last row with data
rnum = LastRow(sh)
'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "B")
' Copy the workbook name in Column A
sh.Cells(rnum + 1, "A").Value = Right(FName(N), Len(FName(N) - InStrRev(FName(N), "\")))
'sh.Cells(rnum + 1, "A").Value = Left(FName(N), InStrRev(FName(N), ".") - 1)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "Summary", "A1:J500", destrange, False, False
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

[BenChod]

Never mind, I figured it out. Had to use: Dir(path) to get the filename.