File Name Extract From String works every other time

Question

I need help understanding why file name extraction from string only works every other time.

I've tried both right and mid. I've used before in other code with no problem. I'm useing msgbox for debugging.

Final outcome should be adding multiple file names to the bottom of a table

Private Sub ButtonAdd_Click()
    Dim fd As FileDialog
    Dim fName As String ' full path file name
    Dim nextRow As Long
    Dim filename As String ' extracted file name only

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Please select file to add"
    fd.InitialFileName = ThisWorkbook.FullName
    fd.AllowMultiSelect = True
    fchosen = fd.Show

    If fchosen = -1 Then
        For i = 1 To fd.SelectedItems.Count
            fName = fd.SelectedItems(i)
            
            'filename = Right(fName, Len(fName) - InStrRev(filename, "\"))
            filename = Mid(fName, InStrRev(filename, "\") + 1)
            
            MsgBox (filename)
            nextRow = Range("a" & Rows.Count).End(xlUp).row + 1
            'Range("a" & nextRow) = filename
        Next i
    End If
End Sub

Answer 1

As Josh writes in the comments, you are using the wrong variable filename instead of fname. Another example of how nesting commands makes it difficult to find an error and how naming of variables matters.

Split the lines into two pieces and rename fname to something like fullFilename:

Dim fullFilename as string, filename as string, p as long
fullFilename = fd.SelectedItems(i)
p = InStrRev(fullFilename, "\")
if p > 0 then
    filename = mid(fullFilename, p+1)
else
    filename = fullFilename
End If

Now you can easily distinguish between the variable holding the full path and the one that has only the file name. And even if you mix something up, you can easily find the problem using the debugger