Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2006
    Location
    Portland, Oregon, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting only part of a Path (Word 2003 and VBA)

    My information is coming from a dialog box. The input box will be filled in my the End User. They will enter something like "JonesCorrespondence"
    In my routine, that information will look like U:CRQJonesCorrespondence. I will need the entire "JonesCorrespondence", but for part of my routine I only want "Jones". I've tried a number of things. Do I want a Left Function, a Right function? I'm stuck.
    Thanks
    Charlotte Quiroz

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    This shows you how to get all of the text up to and not including the first
    I have used Application.Pathseparator instead of "", but you could hard code the backslash if you prefer.

    <code>
    Sub GetFolderName()
    Const TestName As String = "JonesCorrespondence"

    Debug.Print Mid(TestName, 1, InStr(TestName, Application.PathSeparator) - 1)

    End Sub
    </code>
    StuartR

  3. #3
    2 Star Lounger
    Join Date
    May 2006
    Location
    Portland, Oregon, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    I'm sorry but I don't know what to do with this?
    The "JonesCorrespondence" will change So, I'm uisng strRetFolderName in place of the path
    And, I'll want to insert it into my routine.
    Define anything?
    Charlotte Quiroz

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    If strRetFolderName and looks something like Stuart's TestName, then modify as below and call as:

    MyFolderName = GetFolderName(strRetFolderName)
    <pre> Function GetFolderName(TestName As String)
    ' Const TestName As String = "JonesCorrespondence"

    GetFolderName = Mid(TestName, 1, InStr(TestName, Application.PathSeparator) - 1)

    End Sub
    </pre>

    If you don't want/ need a dedicated function, you could use the single line of code:

    MyFolderName = Mid(strRetFolderName, 1, InStr(strRetFolderName, Application.PathSeparator) - 1)

    Alan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    Use

    Dim intPos As Integer
    intPos = InStr(strRetFolderName, "")
    If intPos > 0 Then
    strRetFolderName = Left(strRetFolderName, intPos - 1)
    End If

  6. #6
    2 Star Lounger
    Join Date
    May 2006
    Location
    Portland, Oregon, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    Perfect! Worked like a charm. Thank you!
    Charlotte Quiroz

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    Just as an alternative, you could use:
    <pre>Public Function FirstFolder(strInput As String) As String
    FirstFolder = Split(strInput, Application.PathSeparator)(0)
    End Function
    </pre>

    which will return the whole input if there is no backslash in the path.
    FWIW
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    That's cute, I like it.

    StuartR

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts

    Re: Extracting only part of a Path (Word 2003 and VBA)

    I like to reduce everything to one line if at all possible! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting only part of a Path (Word 2003 and

    I think a minor adjustment is needed to ignore the drive letter or share name or other prefatory material indicated in the original scenario:

    <code>Public Function FirstFolder( _
    strInput As String, _
    Optional strIgnore As String = vbNullString) As String
    FirstFolder = Split(Replace(strInput, strIgnore, vbNullString), Application.PathSeparator)(0)
    End Function

    Sub Test_FirstFolder()
    MsgBox FirstFolder("c:firstlevelsecondlevelfilename.doc")
    MsgBox FirstFolder("c:firstlevelsecondlevelfilename.doc", "c:firstlevel")
    End Sub</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •