Trimming varaible text strings using VBA

Mc Culloch

Board Regular
Joined
Jul 21, 2005
Messages
68
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!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,065
Office Version
  1. 2016
Platform
  1. Windows
There is probably a better way using XL native Functions rather than the InStrRev VBA one but here you go:

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

Regards.
 

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
rafaaj2000 said:
There is probably a better way using XL native Functions rather than the InStrRev VBA one but here you go:

Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,065
Office Version
  1. 2016
Platform
  1. Windows
You can try this :

Code:
Sub Test()

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

Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,521
Messages
5,572,630
Members
412,475
Latest member
JaredNAU
Top