If all you want is the filename at the end, I'd just use Split:
Dim strFile as String, varData
varData = Split(Worksheets("Sheet1").Range("A1").Value, "\")
strFile = varData(Ubound(varData))