Results 1 to 13 of 13
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding the last \ in a string (97)

    Sorry if this is a repeated common question, lack of Search etc.

    I have a file path set as a string for example:

    cocuments and settingsdesktopjezzareport.csv

    I am just trying to get the file name report.csv. What is the code to hunt through the original string to find the last so that I can just get the value report.csv. I seem to remeber it uses the right, left and len functions and have seen it in the forum before.
    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding the last \ in a string (97)

    If the string is in A1 this will give the file name:
    <pre>=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"",CHAR(1) ,LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))</pre>


    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the last \ in a string (97)

    Hi Steve

    Thanks for your speedy reply. I have this running in VBA

    ......

    FileToOpen = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , " Source File") ' This finds the file to be opened

    Workbooks.Open FileToOpen

    .........

    Obviously FileToOpen is the file path

    The VBA doesn't like the substitute function... am I doing something wrong?

    Jerry
    Jerry

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

    Re: Finding the last \ in a string (97)

    Dim FileToOpen As String
    Dim FileName As String
    Dim i As Integer

    FileToOpen = Application.GetOpenFileName(...)
    For i = Len(FileToOpen) To 1 Step -1
    If Mid(FileToOpen, i, 1) = "" Then
    Exit For
    End If
    Next i

    If i = 0 Then
    MsgBox "No found in path!"
    Else
    MsgBox Mid(FileToOpen, i+1)
    End If

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Finding the last \ in a string (97)

    Darn it, Hans beat me to it, but I'll offer one useless comment: in my pathetic code, I usually keep the path name and file name as separate strings; it makes them easier to handle, and you can easily concatenate them.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding the last \ in a string (97)

    You got your original question answered. VB is more clear than the megaformula.

    Once you OPEN the file, you can get the path and name directly using:
    sPath = activeworkbook.path
    sFilename = activeworkbook.name

    FYI,
    To use substitute (and other "non-VB" functions preface them with:
    "Application.worksheetfunction." eg:
    <pre>Application.worksheetfunction.SUBSTITUTE(text ,old_text,new_text,instance_num)</pre>


    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the last \ in a string (97)

    Slightly easier and more efficient than Hans' code:

    <pre>Dim vPath As Variant
    Dim strFName As String
    vPath = Application.GetOpenFilename
    If Not vPath = False Then
    strFName = Dir(vPath)
    MsgBox "File name is " & strFName
    End If
    </pre>

    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the last \ in a string (97)

    Hi Hans

    I had one of the 11.30pm inspirations last night and went tapping at the laptop. I got a solution not as elegant as yours but I thought I would share it with the forum, just to show I am trying:

    ........

    While Left(FileName,1) <> ""

    x=x+1

    FileName=Right(FileToOpen,x)

    Wend

    FileName = Right (FileToOpen, x-1)
    FileLength = Len (FileName)
    NewFileName = Left (FileName, FileLength-4)

    ...... etc

    I think I was nearly there, it just seems you have used Step/Next
    Jerry

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

    Re: Finding the last \ in a string (97)

    Hi Jerry,

    There is no fundamental reason to prefer a For / Next loop over a While loop (or vice versa). Both will work fine.

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the last \ in a string (97)

    InStrRev() finds a string within another one starting from the end, so you could use:
    <pre>iPos = InStrRev(FileToOpen, "")
    strPathName = Left(FileToOpen, iPos - 1)
    strFileName = Right(FileToOpen, Len(FileToOpen) - iPos)
    </pre>

    Have fun!
    Ian.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding the last \ in a string (97)

    I can't seem to find this function in the VB I have. InStr and InStrB but no InStrRev

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the last \ in a string (97)

    I believe that InStrRev was introduced with Office 2K

    Peter

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the last \ in a string (97)

    Sorry <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> - I use Excel 2000 now, but I thought I remembered InStrRev being in earlier versions.

    Memory going with advancing age, I'm afraid!

    Ian.

Posting Permissions

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