Trimming varaible text strings using VBA

[Mc Culloch]

Got a problem that I can't work out how to do using the regular TEXT functions.

I have a text string which represents a files path name [returned using "Application.GetOpenFileName()"]. For example,

C:\WorkFiles\2005_06\Nov\Reports.xls

I need to be able to trim off the file name, leaving the full path down to the folder. The example above would be as follows,

C:\WorkFiles\2005_06\Nov\

The logic I was thinking of wouyld be to trim off all the chars from right to left until you reach the first \ but I can't get my head round how you would do that using the standard functions.

Any ideas gratefully received!

 

[Jaafar Tribak]

There is probably a better way using XL native Functions rather than the InStrRev VBA one but here you go:

Sub Test()
    strPath = "C:\FolderMain\Next\Book.xls"
    MsgBox Left(strPath, InStrRev(strPath, "\"))
End Sub

Regards.

 

[Mc Culloch]

Thanks, works a treat.

 

[MrT82]

There is probably a better way using XL native Functions rather than the InStrRev VBA one but here you go:

Sub Test()
    strPath = "C:\FolderMain\Next\Book.xls"
    MsgBox Left(strPath, InStrRev(strPath, "\"))
End Sub

Regards.

Hi Jafaar,

Sorry to be thick, how would you do this is you didn't want the file path but just the file name?

Thanks,

Paul

 

[Jaafar Tribak]

You can try this :

Sub Test()

   strPath = "C:\FolderMain\Next\Book.xls"
   MsgBox Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
    
End Sub

Regards.