Need help editing a string

[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

 

[Norman Jones]

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

 

[Tim Williams]

or this...

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

Tim.

 

[Norman Jones]

Hi Tim,

Just to alert you to a minor typo:

GetLast = Split(sPath, "\")(UBound(Split(sPath, "\")))

should be:

GetLastPart = Split(sPath, "\")(UBound(Split(sPath, "\")))

 

[Steve Roberts]

That worked Great! Thanks.

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

 

[Tim Williams]

Thanks Norman

Tim

Hi Tim,

Just to alert you to a minor typo:


should be:

GetLastPart = Split(sPath, "\")(UBound(Split(sPath, "\")))