How to isolate a folder's name in a path

[Carlos Chalhoub]

Hi listmates,

I got this long path:
\\Solcorpiis\Insight\Product Information\Products\Project
Documentation\Release 6.4.0\Functionals\Reports\Data_Reports\file047.txt

Thanks to an earlier tip, I was able to isolate the different folders' names
with Split(sPath, "\"). The problem is the path may change and the results
of the split become a moving target. The only constant folder is the
"Functionals" one. Anything before or after it may change (the name and
number of the folders). What I need to do is locate "Functionals" among the
different split folders and assign each folder name following it to a string
variable. In this case, that would be Reports and Data_Reports. How can that
be done? Should I be using Split or some other formula?

Thanks.
Carlos

 

[Jonathan West]

Hi listmates,

I got this long path:
\\Solcorpiis\Insight\Product Information\Products\Project
Documentation\Release 6.4.0\Functionals\Reports\Data_Reports\file047.txt

Thanks to an earlier tip, I was able to isolate the different folders' names
with Split(sPath, "\"). The problem is the path may change and the results
of the split become a moving target. The only constant folder is the
"Functionals" one. Anything before or after it may change (the name and
number of the folders). What I need to do is locate "Functionals" among the
different split folders and assign each folder name following it to a string
variable. In this case, that would be Reports and Data_Reports. How can that
be done? Should I be using Split or some other formula?

InStr & InstrRev would be useful in this case. The following function will
do something along the lines you want.

Function SplitFoldersPastFunctionals(byVal sPath as String) as Variant
Dim iPos as Long

'Find the position of the Functionals folder
iPos = Instr(sPath, "\Functionals\")
If iPos = 0 Then
'Functionals not in the string, return an empty variant
Exit Function
Else
'chop off the leading part of the string
sPath = Mid$(sPath, iPos + Len("\Functionals\"))

'find the last path separator
iPos = InstrRev(sPath, "\")
If iPos = 0 Then
'file is directly in Functionals folder, return the filename
SplitFoldersPastFunctionals = sPath
Exit Function
Else
'chop off filename
sPath = Left$(sPath, iPos - 1))

'split the remaining string
SplitFoldersPastFunctionals = Split(sPath, "\")
End If
End If
End Function

 

[Carlos Chalhoub]

Hi again,

I was able to load the following into 2 string variables:

docPath = \\Solcorpiis\Insight\Product Information\Products\Project
Documentation\Release 6.4.0\Functionals\Reports\Data_Reports\file047.txt

AND

aPath = \\Solcorpiis\Insight\Product Information\Products\Project
Documentation\Release 6.4.0\Functionals

That means, I only need to get the value of (docPath - aPath). If I can get
this into a variable string, then I'm home free.

Any ideas?
Carlos

 

[Carlos Chalhoub]

Thanks Jonathan, It worked great.

It is amazing what you can do with Len, Mid, Left, Right, Instr, InstrRev,
when you know how to put them together.

What exactly is the difference between Left and Left$ or Mid and Mid$?

Thanks, again.

 

[Jonathan West]

Thanks Jonathan, It worked great.

It is amazing what you can do with Len, Mid, Left, Right, Instr, InstrRev,
when you know how to put them together.

What exactly is the difference between Left and Left$ or Mid and Mid$?

Left$ returns a string. Left returns a Variant of subtype string. If you are
assigning the result of the function to a string variable, using Left$ will
be quicker than using Left because you avoid a round trip conversion between
string and variant. Likewise, if you are assigning the result of the
function to a variant, using Left will be quicker.