Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Hi All
What is the the most efficient way of of extracting the path and file elements from a text string? I have a full path string such as (the path length and file type can change) D:\Development Path\Main Files\Test Procedures\Sample Data.mdb What I would like to end up with is two string variables xPath and xFile xPath = D:\Development Path\Main Files\Test Procedures\ xFile = Sample Data.mdb TIA Cheers Nigel ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
One way that is working form 97-2003
Sub test() Dim Str As String Dim vArr As Variant Dim sFname As String Dim xFile As String Dim XPath As String Str = "D:\Development Path\Main Files\Test Procedures\Sample Data.mdb" vArr = Split97(Str, "\") sFname = vArr(UBound(vArr)) XPath = Left(Str, Len(Str) - Len(sFname)) xFile = sFname MsgBox xFile MsgBox XPath End Sub Function Split97(sStr As Variant, sdelim As String) As Variant ' Tom Ogilvy Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Nigel" wrote in message ... Hi All What is the the most efficient way of of extracting the path and file elements from a text string? I have a full path string such as (the path length and file type can change) D:\Development Path\Main Files\Test Procedures\Sample Data.mdb What I would like to end up with is two string variables xPath and xFile xPath = D:\Development Path\Main Files\Test Procedures\ xFile = Sample Data.mdb TIA Cheers Nigel ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Thanks Ron, I am developing in Excel 2002 but need it to run in Excel97, so
this code is perfect. Cheers Nigel "Ron de Bruin" wrote in message ... One way that is working form 97-2003 Sub test() Dim Str As String Dim vArr As Variant Dim sFname As String Dim xFile As String Dim XPath As String Str = "D:\Development Path\Main Files\Test Procedures\Sample Data.mdb" vArr = Split97(Str, "\") sFname = vArr(UBound(vArr)) XPath = Left(Str, Len(Str) - Len(sFname)) xFile = sFname MsgBox xFile MsgBox XPath End Sub Function Split97(sStr As Variant, sdelim As String) As Variant ' Tom Ogilvy Split97 = Evaluate("{""" & _ Application.Substitute(sStr, sdelim, """,""") & """}") End Function -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Nigel" wrote in message ... Hi All What is the the most efficient way of of extracting the path and file elements from a text string? I have a full path string such as (the path length and file type can change) D:\Development Path\Main Files\Test Procedures\Sample Data.mdb What I would like to end up with is two string variables xPath and xFile xPath = D:\Development Path\Main Files\Test Procedures\ xFile = Sample Data.mdb TIA Cheers Nigel ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Nigel,
If you have Excel 2000 or late, you can use InstrRev, and look for the last \. Check it out in VBA Help. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Hi All What is the the most efficient way of of extracting the path and file elements from a text string? I have a full path string such as (the path length and file type can change) D:\Development Path\Main Files\Test Procedures\Sample Data.mdb What I would like to end up with is two string variables xPath and xFile xPath = D:\Development Path\Main Files\Test Procedures\ xFile = Sample Data.mdb TIA Cheers Nigel ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Bob,
Thanks for the note, my problem was that I am developing in Excel 2002 but need to run it Excel 97. Ron de Bruin reply gives me the answer. Cheers Nigel "Bob Phillips" wrote in message ... Nigel, If you have Excel 2000 or late, you can use InstrRev, and look for the last \. Check it out in VBA Help. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Hi All What is the the most efficient way of of extracting the path and file elements from a text string? I have a full path string such as (the path length and file type can change) D:\Development Path\Main Files\Test Procedures\Sample Data.mdb What I would like to end up with is two string variables xPath and xFile xPath = D:\Development Path\Main Files\Test Procedures\ xFile = Sample Data.mdb TIA Cheers Nigel ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Nigel,
ah well, keep it up your sleeve for later. Bob "Nigel" wrote in message ... Bob, Thanks for the note, my problem was that I am developing in Excel 2002 but need to run it Excel 97. Ron de Bruin reply gives me the answer. Cheers Nigel |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Another way ..
Sub TesterII() Dim str As String Dim xfile As String Dim xpath As String str = "D:\Development Path\Main Files\Test Procedures\Sample Data.mdb" xfile = Dir(str) xpath = Left(str, Len(str) - Len(xfile)) MsgBox xfile MsgBox xpath End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stripping Path from File String
Nice!
-- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Another way .. Sub TesterII() Dim str As String Dim xfile As String Dim xpath As String str = "D:\Development Path\Main Files\Test Procedures\Sample Data.mdb" xfile = Dir(str) xpath = Left(str, Len(str) - Len(xfile)) MsgBox xfile MsgBox xpath End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate path and file string for external reference | Excel Worksheet Functions | |||
Relative Path in Connection String | Excel Discussion (Misc queries) | |||
How to extract only file name from folder path string in Excel? | Excel Worksheet Functions | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Using a variable string in a file save path | Excel Programming |