MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help With a function!


Posted by RoB on November 12, 2001 2:31 PM

I have this function that Dank wrote for me, and it works great, but I'm having some trouble understanding it exactly. Could someone explain to me more clearly how it works? Currently, it extracts the file name from a given directory ie: in the directory "C:\Temp\10-01\Test.xls", it would return "Test". I'm trying to modify it to get one of the directory names, ie: in the above directory, the function will return "10-01", but I'm not sure how to modify it because Im not sure how it works. Ive tried playing with it, with no success. I appreciate any help, thanks. Heres the function:

Function FilenameOnly(strPath As String) As String
Dim lngCharCounter As Long
'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
End Function


Posted by faster on November 12, 2001 2:56 PM

This function should do it:

Function UpDir(MyString)
Dim Pos1, Pos2, i, j

'set Pos2 value
For i = Len(MyString) To 0 Step -1
If Mid(MyString, i, 1) = "\" Then
Pos2 = i - 1
Exit For
End If
Next i

'set Pos1 value
For j = Pos2 To 0 Step -1
If Mid(MyString, j, 1) = "\" Then
Pos1 = j + 1
Exit For
End If
Next j

'return value
UpDir = Mid(MyString, Pos1, 1 + Pos2 - Pos1)

End Function

Posted by RoB on November 12, 2001 4:52 PM

Thats works but could you explain a little?

I'm pretty new to vba, and was wondering if you could explain a little how this is working. Thanks

Posted by faster on November 13, 2001 9:48 AM

Re: Thats works but could you explain a little?

I will try. Do you have any specific questions?


Function UpDir(MyString)
'declare variables to use in function
'Pos1 variable to holds start position in string
'Pos1 variable to holds end position in string
'i and j are just counters

Dim Pos1, Pos2, i, j

'set Pos2 value
'start at the end of the string and move to forward
'check each character for "\"
'the length of the string is stored in i
'if "\" is not found then i = i - 1


For i = Len(MyString) To 0 Step -1
If Mid(MyString, i, 1) = "\" Then
Pos2 = i - 1
Exit For
End If
Next i

'same logic as Pos1 except the start point is Pos1
'set Pos1 value
For j = Pos2 To 0 Step -1
If Mid(MyString, j, 1) = "\" Then
Pos1 = j + 1
Exit For
End If
Next j


'return value
UpDir = Mid(MyString, Pos1, 1 + Pos2 - Pos1)

End Function