File Path Find

[Melissa]

Using Word XP VBA I need to be able to pull the folder
name from the 2nd from last level of the file path:-

H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc

to return Project Strategy

H:\Work\reports\customername\2004
\Financial\standard\marketing.doc

to return Financial

H:\Work\reports\customername\2003\Health &
Safety\standard\marketing.doc

to return Health & Safety

I have looked at trimming a path to just show the document
name which I am fine with but as for this query I am
really stuck - currently the full document path is being
stored in a variable.

Does anyone have any ideas please.

Thank you for your time.

 

[Malcolm Smith]

Melissa

What you could do is to use the Split$() function to put that string into
an array and define the "\" character as the delimiter.

Then do a UBound() on that array to determine how many elements you have
in there.

Then it's a piece of pie to concatenate as many elements as you like into
another string with a simple loop, or in your case to pull out the
UBound()-2 element from the list.

Easy as cake!

- Malc
www.dragondrop.com

 

[Helmut Weber]

Hi Melissa,
e.g. (beware of line brakes by the newsreader)
Dim s As String
Dim a() As String
s = "H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc"
a = (Split(s, "\"))
MsgBox a(UBound(a) - 2)

 

[Dave Lett]

Hi Melissa,

You can use something like the following:

Dim sName As String
Dim iLastVirgule As Integer
Dim iPenultimateVirgule As Integer
Dim iThirdVirgule As Integer

sName = "H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc"
'''sName =
"H:\Work\reports\customername\2004\Financial\standard\marketing.doc"
'''sName = "H:\Work\reports\customername\2003\Health &
Safety\standard\marketing.doc"
iLastVirgule = InStrRev(sName, "\") - 1
iPenultimateVirgule = InStrRev(Left(sName, iLastVirgule), "\") - 1
iThirdVirgule = InStrRev(Left(sName, iPenultimateVirgule), "\")
Debug.Print Mid(sName, iThirdVirgule + 1, iPenultimateVirgule -
iThirdVirgule)


You _could_ make this one long statement, but I think that would be a LOT
harder to read:

sName = "H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc"
Debug.Print Mid(sName, _
InStrRev(Left(sName, InStrRev(Left(sName, InStrRev(sName, "\") - 1),
"\") - 1), "\") + 1, _
InStrRev(Left(sName, InStrRev(sName, "\") - 1), "\") - 1 -
InStrRev(Left(sName, InStrRev(Left(sName, InStrRev(sName, "\") - 1), "\") -
1), "\"))

HTH,
Dave

 

[Melissa]

Fantastic - works wonderfully - thanks v v much.

-----Original Message-----
Hi Melissa,

You can use something like the following:

Dim sName As String
Dim iLastVirgule As Integer
Dim iPenultimateVirgule As Integer
Dim iThirdVirgule As Integer

sName = "H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc"
'''sName =
"H:\Work\reports\customername\2004 \Financial\standard\marketing.doc"
'''sName = "H:\Work\reports\customername\2003\Health &
Safety\standard\marketing.doc"
iLastVirgule = InStrRev(sName, "\") - 1
iPenultimateVirgule = InStrRev(Left(sName, iLastVirgule), "\") - 1
iThirdVirgule = InStrRev(Left(sName, iPenultimateVirgule), "\")
Debug.Print Mid(sName, iThirdVirgule + 1, iPenultimateVirgule -
iThirdVirgule)


You _could_ make this one long statement, but I think that would be a LOT
harder to read:

sName = "H:\Work\reports\customername\2004\Project
Strategy\Core\rollout.doc"
Debug.Print Mid(sName, _
InStrRev(Left(sName, InStrRev(Left(sName, InStrRev (sName, "\") - 1),
"\") - 1), "\") + 1, _
InStrRev(Left(sName, InStrRev(sName, "\") - 1), "\") - 1 -
InStrRev(Left(sName, InStrRev(Left(sName, InStrRev (sName, "\") - 1), "\") -
1), "\"))

HTH,
Dave



.

 

[Takuon Soho]

Good suggestions.

As for the English idioms,

Easy as pie!!

Piece of cake!!

(See the movie 2010).

Tak

 

[Malcolm Smith]

I know, I often change the idioms around for the fun of it. I tend
to walk through life with my tongue firmly placed in my cheek!

- Malc