Function for extracting portion of file path

[russiver]

I'm looking for a function (I think I need to use instrRev) to break the following file path:

C:\Documents and Settings\My Documents\Membership Database\MemberPhotos\Member1.bmp

down to

\MemberPhotos\Member1.bmp

ie the file and the subfolder.

Thanks
Russ

 

[mhartman]

Hello;

Yes that is correct. Below is the syntax:

InStrRev Function
Description

Returns the position of an occurrence of one string within another, from the end of string.

Syntax

InstrRev(stringcheck, stringmatch[, start[, compare]])

Regards
Mark

 

[russiver]

Thanks Mark,

But although it is easy to find the first occurence of "\" from the end of the string, I can't figure out how to locate the point of the second occurence - the point at which I want to cut to include the subdirectory.

Russ

 

[mhartman]

Hello:

Search for your string "Member Photos" etc. Then add the slash if you need it.

Regards
Mark

 

[boblarson]

Here you go:

Public Function RetFileFolderInfo(strFilePath As String) As String
    Dim varSplit As Variant
    Dim intUBound As Integer
    
    varSplit = Split(strFilePath, "\", , vbTextCompare)
    intUBound = UBound(varSplit)
    
    RetFileFolderInfo = "\" & varSplit(intUBound - 1) & "\" & varSplit(intUBound)

End Function

 

[russiver]

Thanks guys - both methods now working.

Russ

 

[CraigDolphin]

Well, Bob posted a vba solution while I was working this one out....but for the record...here's a non-vba method

MySubdir: Right([directory],InStr(StrReverse([directory]),"\")+InStr(Right(strReverse([directory]),InstrRev([directory],"\")-1),"\"))

Where directory is the field containing the original text string

 

[mhartman]

Good luck with this!

Regards