none
Excel formula help.... RRS feed

  • Question

  • Currently i have

     

    Sub GetPath()
    Dim s As String
    With ActiveWorkbook
        s = Left(.FullName, InStrRev(.FullName, "\") - 1)
        MsgBox s
    End With
     
    MsgBox ActiveWorkbook.Path
     
    End Sub

    this gives me file path only from the given full file name....

    I also want this in formula if posible...

    Let us say i have full file name in A1, i want in B1 just the path without VBA


    Pedie
    Wednesday, July 27, 2011 8:42 PM

Answers

  • Currently i have

     

    Sub GetPath()
    Dim s As String
    With ActiveWorkbook
        s = Left(.FullName, InStrRev(.FullName, "\") - 1)
        MsgBox s
    End With
     
    MsgBox ActiveWorkbook.Path
     
    End Sub

    this gives me file path only from the given full file name....

    I also want this in formula if posible...

    Let us say i have full file name in A1, i want in B1 just the path without VBA


    Pedie


    See

    Extract last token

    http://www.tushar-mehta.com/publish_train/xl_vba_cases/0131%20Extract%20last%20token.shtml


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    • Marked as answer by Pedie Nz Tuesday, August 2, 2011 9:12 PM
    Wednesday, July 27, 2011 8:44 PM
  • On Wed, 27 Jul 2011 20:42:02 +0000, pedie.me wrote:
     
    >
    >
    >Currently i have
    >
    >  Sub GetPath()
    >Dim s As String
    >With ActiveWorkbook
    >    s = Left(.FullName, InStrRev(.FullName, "\") - 1)
    >    MsgBox s
    >End With
    >MsgBox ActiveWorkbook.Path
    >End Sub
    >
    >
    >this gives me file path only from the given full file name....
    >
    >I also want this in formula if posible...
    >
    >Let us say i have full file name in A1, i want in B1 just the path without VBA
    >Pedie
     
    Here's another solution:
     
    =TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))
     
     

    Ron
    • Marked as answer by Pedie Nz Friday, August 12, 2011 9:39 PM
    Tuesday, August 2, 2011 10:05 PM

All replies

  • Currently i have

     

    Sub GetPath()
    Dim s As String
    With ActiveWorkbook
        s = Left(.FullName, InStrRev(.FullName, "\") - 1)
        MsgBox s
    End With
     
    MsgBox ActiveWorkbook.Path
     
    End Sub

    this gives me file path only from the given full file name....

    I also want this in formula if posible...

    Let us say i have full file name in A1, i want in B1 just the path without VBA


    Pedie


    See

    Extract last token

    http://www.tushar-mehta.com/publish_train/xl_vba_cases/0131%20Extract%20last%20token.shtml


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    • Marked as answer by Pedie Nz Tuesday, August 2, 2011 9:12 PM
    Wednesday, July 27, 2011 8:44 PM
  • Thanks alot for your help....:)
    Pedie
    Tuesday, August 2, 2011 9:13 PM
  • On Wed, 27 Jul 2011 20:42:02 +0000, pedie.me wrote:
     
    >
    >
    >Currently i have
    >
    >  Sub GetPath()
    >Dim s As String
    >With ActiveWorkbook
    >    s = Left(.FullName, InStrRev(.FullName, "\") - 1)
    >    MsgBox s
    >End With
    >MsgBox ActiveWorkbook.Path
    >End Sub
    >
    >
    >this gives me file path only from the given full file name....
    >
    >I also want this in formula if posible...
    >
    >Let us say i have full file name in A1, i want in B1 just the path without VBA
    >Pedie
     
    Here's another solution:
     
    =TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))
     
     

    Ron
    • Marked as answer by Pedie Nz Friday, August 12, 2011 9:39 PM
    Tuesday, August 2, 2011 10:05 PM
  • Thank you...that also works perfect!
    Pedie
    Friday, August 12, 2011 9:39 PM