I need to extract the filename “My Excel File” from Worksheets(“Sheet1”).Range(“A1”), whose value = C:\Documents and Settings\user1\Desktop\My Excel File
I found on Chip Pearson’s site a Function TrimToChar which, using SearchFromRight and a TrimChar of “\” will trim OFF the filename “My Excel File”, leaving the Path.
But, I think I could use his function to tell me the number of characters in the Path and then use that to extract the remaining characters from the total character length (Mid Function)
Problem is, I don’t have a clue how to call a Function in VBA to work on Worksheets(“Sheet1”).Range(“A1”). How do you set the InputText , TrimChar, & SearchFromRight?????
(eventually, this will be in a loop, where I extract the filename from a Dynamic Named Range (list) in Column A)
Public Function TrimToChar(InputText As String, TrimChar As String, _
Optional SearchFromRight As Boolean = False) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TrimToChar
' By Chip Pearson, www.cpearson.com , chip@cpearson.com
' http://www.cpearon.com/excel/SizeString.htm#TrimToChar
'
' This function returns a portion of the string Text that is to the left of
' TrimChar. If SearchFromRight is omitted or False, the returned string
' is that string to the left of the FIRST occurrence of TrimChar. If
' SearchFromRight is True, the returned string is that string to the left of the
' LAST occurrance of TrimToChar. If TrimToChar is not found in the string,
' the entire Text string is returned. TrimChar may be more than one character.
' Comparison is done in Text mode (case does not matter).
' If TrimChar is an empty string, the entire Text string is returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos As Integer
' Test to see if TrimChar is vbNullString. If so, return the whole string. If we
' don't test here and used InStr as in the next logic block, an empty string would
' be returned.
If TrimChar = vbNullString Then
TrimToChar = InputText
Exit Function
End If
' find the position in Text of TrimChar
If SearchFromRight = True Then
Pos = InStrRev(InputText, TrimChar, -1, vbTextCompare)
Else
Pos = InStr(1, InputText, TrimChar, vbTextCompare)
End If
' return the sub string
If Pos > 0 Then
TrimToChar = Left(InputText, Pos - 1)
Else
TrimToChar = InputText
End If
End Function
using XL2000
Bookmarks