Need help editing a string
 

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

Need help editing a string
 

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


---
Regards,
Norman



"Steve Roberts" wrote in message
...
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

Need help editing a string
 

or this...

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

Tim.

"Steve Roberts" wrote in message
...
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

Need help editing a string
 

Hi Tim,

Just to alert you to a minor typo:

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

should be:

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


---
Regards,
Norman



"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...

or this...

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

Tim.

"Steve Roberts" wrote in message
...
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

Need help editing a string
 

That worked Great! Thanks.


"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Steve Roberts" wrote in message
...
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

Need help editing a string
 

Thanks Norman

Tim

"Norman Jones" wrote in message
...
Hi Tim,

Just to alert you to a minor typo:

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

should be:

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


---
Regards,
Norman