Trancat file name using VBA

[Lillian]

I have one excel spreed sheet, it have three worksheet,
they are sheet1, sheet2, sheet3, in each worksheets on
the column B 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
all the file name but in different directory, 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

 

[losmac]

It's simple...

Sub djkslajdksla()
MsgBox ExtractFileName(ThisWorkbook.FullName)
End Sub

Function ExtractFileName(fPath As String) As String
Dim i As Long, lcut As Long, llen As Long

i = InStr(i + 1, fPath, "\")
Do While i > 0
lcut = i
i = InStr(i + 1, fPath, "\")
Loop

If lcut > 0 Then
llen = Len(fPath) - lcut
ExtractFileName = Mid(fPath, lcut + 1, llen)
Else
ExtractFileName = ""
End If
End Function

 

[Lillian]

Hi, this macro did not mention the loop for sheet1,
sheet2, sheet3, also on each sheet of column B need to do
the macro, you did not mention to column B at all, how to
run your macro, do I hight light the column B and run it?


Lillian

 

[Lillian]

After I run this macro file name update.xls was popup
from the message box, after that how to run your
function, need help.

thanks.

Lillian

 

[Dick Kusleika]

Lillian

What do you want to do with the filename once you have it? If you want to
put it in a cell, then put the function in the cell. So if your long file
path is in B1 and you just want the file name in C1, then in C1, put

=ExtractFileName(B1)

and repeat that procedure for every cell, regardless of which sheet it's on.

If you want to do something with the file name in a macro, then store the
result of the function in a variable.

Sub GetAllFilenames()

Dim sh as Worksheet
Dim ShortName as String
Dim cell as Range

For Each sh in ThisWorkbook.Worksheets
For Each Cell in sh.Columns("B").Cells
ShortName = ExtractFileName(cell.Value)
'Do something with ShortName
Next Cell
Next sh

End Sub

If you want to replace all the existing entries with just the filename, then
use a macro like this

Sub ReplacePaths()

Dim sh as Worksheet
Dim cell as Range

For Each sh in ThisWorkbook.Worksheets
For Each cell in Intersect(sh.Columns("B"),sh.UsedRange).Cells
If Instr(1,cell.Value,"\") > 0 Then
cell.Value = ExtractFileName(cell.Value)
End If
Next cell
Next sh

End Sub