code to strip back (from the right) a directory but not the file name
 

I have a path
eg. s:\jobs\2003\12345\construction\file.xls

I want to find the code that strips a level of this path
out i.e. remove \construction. (Go up a directory)
eg. s:\jobs\2003\12345\file.xls

This is so my destination file can be automatically
relinked to a source file that is kept in the directory
above the current destination files directory.

Thank you very much
Chrissie.

code to strip back (from the right) a directory but not the file name
 

Chrissie,

Try something like the following:

Dim Pos1 As Long
Dim Pos2 As Long
Dim S As String
S = "s:\jobs\2003\12345\construction\file.xls"
Pos1 = InStrRev(S, "\", -1, vbTextCompare)
Pos2 = InStrRev(S, "\", Pos1 - 1, vbTextCompare)
If Pos2 0 Then
S = Left(S, Pos2 - 1) & Mid(S, Pos1)
End If
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"chrissie frisbee" wrote in message
...
I have a path
eg. s:\jobs\2003\12345\construction\file.xls

I want to find the code that strips a level of this path
out i.e. remove \construction. (Go up a directory)
eg. s:\jobs\2003\12345\file.xls

This is so my destination file can be automatically
relinked to a source file that is kept in the directory
above the current destination files directory.

Thank you very much
Chrissie.

code to strip back (from the right) a directory but not the file name
 

s:\jobs\2003\12345\construction\file.xls

to go up one directory use two dots.

s:\jobs\2003\12345\construction\..\file.xls

so
sPath = Thisworkbook.Path & "\..\"
sName = sPath & "file.xls"

--
Regards,
Tom Ogilvy


"chrissie frisbee" wrote in message
...
I have a path
eg. s:\jobs\2003\12345\construction\file.xls

I want to find the code that strips a level of this path
out i.e. remove \construction. (Go up a directory)
eg. s:\jobs\2003\12345\file.xls

This is so my destination file can be automatically
relinked to a source file that is kept in the directory
above the current destination files directory.

Thank you very much
Chrissie.