Need help editing a string

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

Steve Roberts

The code below prompts the user for a folder then trys to use that folder
path to name a worksheet. Of course it doesn't work because it has invalid
characters for naming a worksheet. What I need to do is cut the folder path
down to just the name of the last folder ie.. c:\documents and
settings\user\desktop would get cut down to just desktop. I have no idea
where to start.

Thanks in advance

Steve


With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)
Dim curwb As Workbook
Dim curws As Worksheet
MyFolder = "c:\"
Set curwb = ActiveWorkbook
Set curws = curwb.Worksheets.Add
curws.Name = .SelectedItems(1)
Me.txtSelectedFolder = .SelectedItems(1)

End With
 
Hi Steve,

Assuming that you are using xl2k or later, try something like:

Sub Tester()
Dim sStr As String
Dim iPos As Long

sStr = "c:\documents andsettings\user\desktop"
iPos = InStrRev(sStr, "\")
sStr = Mid(sStr, iPos + 1)
MsgBox sStr

End Sub
 
or this...

Function GetLastPart(sPath)
GetLast = Split(sPath, "\")(UBound(Split(sPath, "\")))
End Function

Tim.
 
Hi Tim,

Just to alert you to a minor typo:
GetLast = Split(sPath, "\")(UBound(Split(sPath, "\")))
Click to expand...

should be:

GetLastPart = Split(sPath, "\")(UBound(Split(sPath, "\")))
 
That worked Great! Thanks.


Norman Jones said:
Hi Steve,

Assuming that you are using xl2k or later, try something like:

Sub Tester()
Dim sStr As String
Dim iPos As Long

sStr = "c:\documents andsettings\user\desktop"
iPos = InStrRev(sStr, "\")
sStr = Mid(sStr, iPos + 1)
MsgBox sStr

End Sub
Click to expand...
 
Back
Top