ok, here is the function I combined from from different functions Ive gathered from everyone on the board (thanks
)
Function FilenameOnly(strPath As String) As String
Dim lngCharCounter As Long
Dim Loan_Officer_Dir, Date_Dir As String
'First establish the filename and extenstion part
For lngCharCounter = Len(strPath) To 1 Step -1
If Mid(strPath, lngCharCounter, 1) = "" Then
FilenameOnly = Right(strPath, Len(strPath) - lngCharCounter)
'Remove the extension
FilenameOnly = Left(FilenameOnly, InStr(1, FilenameOnly, ".") - 1)
Exit For
End If
Next lngCharCounter
'set Pos1 value
For i = Len(MyString) To 0 Step -1
If Mid(MyString, i, 1) = "" Then
Pos1 = i - 1
Exit For
End If
Next i
'set Pos2 value
For j = Pos1 To 0 Step -1
If Mid(MyString, j, 1) = "" Then
Pos2 = j - 1
Exit For
End If
Next j
'set Pos3 value
For k = Pos2 To 0 Step -1
If Mid(MyString, k, 1) = "" Then
Pos3 = k + 1
Exit For
End If
Next k
Loan_Officer_Dir = Mid(MyString, Pos2, 1 + Pos1 - Pos2)
Date_Dir = Mid(MyString, Pos3, 1 + Pos2 - Pos3)
End Function
Then I refer to the FilenameOnly function in my macro with:
Cells(i + 4, 1) = FilenameOnly(.FoundFiles(i))
i tried referring to the Loan_Officer_Dir variable in my function with:
Cells(i + 4, 2) = Loan_Officer_Dir(.FoundFiles(i))
but that didnt work. I used to have a total different function for Loan_Officer_Dir, but i noticed that the functions were very similar so im trying to consolidate on the code and put these 3 functions into 1. I just dont know how to refer to them correctly.
Thanks