Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Importing/Exporting text files and string editing | Excel Programming | |||
Passing a String in Array to Range as String | Excel Programming | |||
Create a formula into a String then assign string to a cell | Excel Programming |