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 =---

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 =---

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
=---

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 =---

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 =---

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

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

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

Nice!

indeed

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Tushar Mehta" wrote in message news:MPG.1a9aaa7e4547571d9896ed@news-server...
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

You don't think doing a file io to get the filename is a bit slow?

Don't get me wrong, I have used Dir myself when I am lazy, but . . .
--
Regards,
Tom Ogilvy

"Tushar Mehta" wrote in message
news:MPG.1a9aaa7e4547571d9896ed@news-server...
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

Agreed, but it will work only if the file exists on the user's machine. If
it's just a string- parsing exercise without real files (or with files on
another machine), Dir will yield a blank.

--

Vasant


"Tushar Mehta" wrote in message
news:MPG.1a9aaa7e4547571d9896ed@news-server...
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

Hi Tom,

Nice in its simplicity, is what I meant.

You got me doing some analysis (totally unwarranted given all that I
had to do yesterday <g)

For 27,800+change files,
DIR() took 2.5 seconds
Evaluate() 1.5 seconds
StrReverse(Left(Instr(StrReverse(...)))) 0.2 seconds
a custom function with a while loop to find the last occurence of the
token of interest < 0.1 seconds.

--
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...
You don't think doing a file io to get the filename is a bit slow?

Don't get me wrong, I have used Dir myself when I am lazy, but . . .

Hi Guys,
I think I am glad I asked the question! I certainly got a comprehensive
answer.

In view of the single file I am acting upon, the method might just be
academic in terms of speed, but it's good to know the alternatives.

Cheers
Nigel

"Tushar Mehta" wrote in message
news:MPG.1a9bcc83c6c4e5f09896ef@news-server...
Hi Tom,

Nice in its simplicity, is what I meant.

You got me doing some analysis (totally unwarranted given all that I
had to do yesterday <g)

For 27,800+change files,
DIR() took 2.5 seconds
Evaluate() 1.5 seconds
StrReverse(Left(Instr(StrReverse(...)))) 0.2 seconds
a custom function with a while loop to find the last occurence of the
token of interest < 0.1 seconds.

--
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...
You don't think doing a file io to get the filename is a bit slow?

Don't get me wrong, I have used Dir myself when I am lazy, but . . .





----== 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 =---

Thanks for the research. I usually use the while loop myself.

I figured there was overhead with evaluate - but didn't think as much as
with fileio - so you at least confirmed that. Still, it appears to be a
dog. That might be insightful for those that choose to use [A1] notation.
I played with that a while back and found it about 14 times slower than
Range("A1") type notation.


--
Regards,
Tom Ogilvy

"Tushar Mehta" wrote in message
news:MPG.1a9bcc83c6c4e5f09896ef@news-server...
Hi Tom,

Nice in its simplicity, is what I meant.

You got me doing some analysis (totally unwarranted given all that I
had to do yesterday <g)

For 27,800+change files,
DIR() took 2.5 seconds
Evaluate() 1.5 seconds
StrReverse(Left(Instr(StrReverse(...)))) 0.2 seconds
a custom function with a while loop to find the last occurence of the
token of interest < 0.1 seconds.

--
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...
You don't think doing a file io to get the filename is a bit slow?

Don't get me wrong, I have used Dir myself when I am lazy, but . . .

Based on some code proposed by one of the newest MS MVPs, Colo...

10,000 loops consisting of 4 statements, each setting the same variable
to a different range:
Time Rank
Set r = Range("$A$1", "$A$1") 0.0901125 1
Set r = Range("A1", "A1") 0.1058 2
Set r = Cells(1, 1) 0.19815 5
Set r = Range("$A$1") 0.177712499 3
Set r = Range("A1") 0.180887499 4
Set r = [A1] 0.621437508 7
Set r = Cells(1, "A") 0.308837497 6

[The times are the average over 10 cycles and represent the time for
40,000 Set operations and one procedure call, since each test was in a
separate procedure.]

For me most of the results were a surprise.
Range(x,x) was faster than Range (x) by a factor of just under 50%
Cells(m,n) was slower than Range(x) by a bit
[x] was slower than Range(x) by a factor of 4
Cells(m,"x") was even possible, and slower than Cells(m,n) by 50%!

Of course, given that these are timings for 40,000 Set operations, it
won't convince me to stop using Cells() when appropriate to the task at
hand. And, since I never did develop the habit of using [x]... <g

--
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...
Thanks for the research. I usually use the while loop myself.

I figured there was overhead with evaluate - but didn't think as much as
with fileio - so you at least confirmed that. Still, it appears to be a
dog. That might be insightful for those that choose to use [A1] notation.
I played with that a while back and found it about 14 times slower than
Range("A1") type notation.

Thanks Tom and Tushar

Yes, agreed ... using this function i/o is relatively slower
I just put it up there for a different way to do something.
Tushar, thanks for confirming this .... admittedly I don't use this method
opting for the strReverse for Xl2000+ or the loop.

cheers

Ivan

Tushar Mehta wrote in message news:<MPG.1a9bcc83c6c4e5f09896ef@news-server...
Hi Tom,

Nice in its simplicity, is what I meant.

You got me doing some analysis (totally unwarranted given all that I
had to do yesterday <g)

For 27,800+change files,
DIR() took 2.5 seconds
Evaluate() 1.5 seconds
StrReverse(Left(Instr(StrReverse(...)))) 0.2 seconds
a custom function with a while loop to find the last occurence of the
token of interest < 0.1 seconds.

--
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...
You don't think doing a file io to get the filename is a bit slow?

Don't get me wrong, I have used Dir myself when I am lazy, but . . .