Via VBA, How can I obtain the path from a string of text?

[ND Pard]

Version: Excel 2007

I have a 'string' variable named strDbFullName.

The variable contains the full path and file name of an MS Access database.

For example:

strDbFullName = "T:\Bdgt0911\Access\BdgtAdj_0911.mdb"

What is the VBA to retrieve just the path "T:\Bdgt0911\Access"
from the variable strDbFullName?

The variable changes frequently during the program and
may be only 1 sub-folder down from the root.

Thanks in advance.

 

[Bernie Deitrick]

Function MPath(P As String) As String
MPath = Left(P, InStrRev(P, "\")-1)
End Function


Sub test()
MsgBox MPath("T:\Bdgt0911\Access\BdgtAdj_0911.mdb")
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

To imply get the value from your variable strDbFullName's value:

strPath = Left(strDbFullName, InStrRev(strDbFullName , "\")-1)

HTH,
Bernie
MS Excel MVP

 

[Jacob Skaria]

strFile = "T:\Bdgt0911\Access\BdgtAdj_0911.mdb"

'Method 1 using FileSystem Object
Set fso = CreateObject("Scripting.fileSystemObject")
MsgBox fso.GetParentFolderName(strFile)

'MEthod 2 using InstrRev
MsgBox Left(strFile, InStrRev(strFile, "\"))

If this post helps click Yes

 

[ND Pard]

Thanks Bernie and Jacob.

What I was looking for was the 'File System Object' method;
however, the InStrRev function is cool too.

Thanks for the quick responses.