Truncat the file name in long directory using macro
 

Try:

Sub GetFileUntestedCode()
For i=1 to 3
with Sheets(i)
for each x in .range("B1:B" & .range(B65536").end(xlup).row) 'note the
leading "." before Range
x.value=dir(x.value)
Next
End With
Next
End Sub

If you have a valid path in a string, then Dir(thatString) will return the
last piece.

Bob Umlas
Excel MVP


"Lillian" wrote in message
...
I have one excel spreed sheet, it have three worksheet,
they are sheet1, sheet2, sheet3, in each worksheets on
the column B each row has real long file directory,
example:
J:\files\docfiles\amaya01\demand.mcp.wpd, all I need is
last file name: demand.mcp.wpd, it means I only need
the file name after the last slash "\", all the column B
has different rows of file name another example:
j:\FILES\DOCFILES\Civil Service\CS-SUBIA\OLGA.SRP, all
I need is OLGA.SRP file name, so can we write the macro
removed everything before last slash "\".

SO this macro has be in the loop for
sheet1,sheet2,sheet3.

thanks for the help.

Lillian

Truncat the file name in long directory using macro
 

Bob:

I can not run this macro, i and x did not define as
long for something, also do you take care of last
slash "\" in this macro.

thanks.

Lillian
-----Original Message-----
Try:

Sub GetFileUntestedCode()
For i=1 to 3
with Sheets(i)
for each x in .range("B1:B" & .range(B65536").end
(xlup).row) 'note the
leading "." before Range
x.value=dir(x.value)
Next
End With
Next
End Sub

If you have a valid path in a string, then Dir
(thatString) will return the
last piece.

Bob Umlas
Excel MVP


"Lillian" wrote in
message
...
I have one excel spreed sheet, it have three worksheet,
they are sheet1, sheet2, sheet3, in each worksheets on
the column B each row has real long file directory,
example:
J:\files\docfiles\amaya01\demand.mcp.wpd, all I need is
last file name: demand.mcp.wpd, it means I only need
the file name after the last slash "\", all the column
B
has different rows of file name another example:
j:\FILES\DOCFILES\Civil Service\CS-SUBIA\OLGA.SRP, all
I need is OLGA.SRP file name, so can we write the macro
removed everything before last slash "\".

SO this macro has be in the loop for
sheet1,sheet2,sheet3.

thanks for the help.

Lillian



.