How to get file name from long directory

[Lillian]

I have one excel spreed sheet, they are sheet1, sheet2,
sheet3 within this worksheet is called update.xls, on
each sheet1, sheet2, sheet3 in ColumnB each row has real
long directory, example:
J:\files\docfiles\amaya01\demand.mcp.wpd, I need just
last file name, which is after last slash "\"
deemand.mcp.wpd, all entire ColumnB has 1000 rows has
different directory, all I need is the file name, do not
care about the long directory, how to write the macro
just chop out last slash "\" before, and just need the
file name.

So this macro need in the loop for sheet1,sheet2,sheet3.
thanks for the help

Lillian

 

[shockley]

Lillian,
Here's two methods of doing what you want.
HTH,
Shockley


Sub Tester1()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest <> Empty Then
For i = 1 To Len(sTest)
s = Right(sTest, i)
If Left(s, 1) = "\" Then
s = Right(s, Len(s) - 1)
Exit For
End If
Next i
.Cells(cell.Row, 3) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub

Sub Tester2()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest <> Empty Then
OldSlashPos = 0
Do
SlashPos = InStr(1, sTest, "\")
If SlashPos > 0 Then
OldSlashPos = OldSlashPos + SlashPos
sTest = Mid(sTest, SlashPos + 1)
Else: Exit Do
End If
Loop
.Cells(cell.Row, 2) = sTest
Else: Exit For
End If
Next cell
End With
Next j
End Sub

 

[Lillian]

Shockley:
When I run this macro, it complain that j variable not
define, which I define as long, then complain sName not
define, can you help me more on this, thanks.


Lillian

 

[shockley]

Lillian,

It sounds like you have your vb Editor set to require variable declaration.
Do you see "Option Explicit" at the top of the module? Remove that
statement, and the macros should run OK, but let me know if they don't.

You can turn off this option in the vb Editor with

Tools | Options | Editor tab | Unselect "Require Variable Declaration

Or you can leave it on and declare every variable. If you leave it on, every
time you insert a new module you will see the "Option Explicit" statement at
the top. For beginners, it is recommended that it be used, as it saves lots
of time debugging silly errors like spelling that are easily overlooked.

Regards,
Shockley

 

[David Turner]

Lillian wrote

When I run this macro, it complain that j variable not
define, which I define as long, then complain sName not
define, can you help me more on this, thanks.

Try (temporarily at least until you can get all variables declared)
unchecking 'Require Variable Declaration' in VBE's Tools|Options|Editor

 

[Lillian]

Shockley:
I define as follow
Dim j As Long, sName As String, cell As Range, sTest As
String, i As Long, s As String
the macro run, but it overlay on the ColumnC, so can we
add one more code to insert blank Column on columnC first
then run this macro, otherwise I need to manually insert
blank column on each sheets, then run, it work. I use
Tester1() run, also can you explain each line of your
coding, I really appreciate your help.

Lillian

 

[Lillian]

Shockley:

Hi, I forgot to tell you that once the file name has
been replaced in Column C, then Column B need to be delete
as well. because we already got the result we need in
column C we do not need column B any more. so we need add
one more code for insert blank column into Column C and
and one more code for deleted Column B for the final, how
we add this two line in between your macro.

Thank you so much for the help, you are great.


Lillian

 

[shockley]

Lillian,

Below is the complete annotated version. I've added a line to insert a
column so that the original full path ends up in column 3 and the file name
goes in column 2, which is how I think you want it. If you would rather just
overwrite the full path with the filename in column 2, I put a version that
will do that below the annotated version (it just involves changing one
column number and removing the insert command).

To round out your understanding of how the code works I recommend you look
up in the vb editor Help! some of the special commands and functions used in
the macro (use the animated help utility), which I list below:
For Each
Len
Right
Left
With

It also helps to use F8 to walk through the macro line by line, placing your
cursor over variables as you go to see how their values change.

Regards,
Shockley


Sub Tester1()
'Loop through the sheets:
For j = 1 To 3
'Assign current-sheet name to a variable:
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
.Columns(1).Insert
'Loop through the collection of cells _
in Column 3. 'cell' becomes a range _
variable:
For Each cell In .Columns(3).Cells
'Assign the full path to a string variable:
sTest = cell.Text
'Jump out of the For loop _
at the first empty cell and go to _
the next sheet ('next j'):
If sTest <> Empty Then
'Create a string 's' of incrementing _
length, beginning with the _
right-most character of sTest and _
adding one character with each _
iteration of the loop, _
each time testing _
whether the added character is _
the slash character. When it is _
the slash character, remove it _
from the string, and the remaining _
string is your result (the string _
variable 's'):
For i = 1 To Len(sTest)
s = Right(sTest, i)
If Left(s, 1) = "\" Then
s = Right(s, Len(s) - 1)
Exit For
End If
Next i
'Put the result in Column 2:
.Cells(cell.Row, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub


Sub Tester1()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest <> Empty Then
For i = 1 To Len(sTest)
s = Right(sTest, i)
If Left(s, 1) = "\" Then
s = Right(s, Len(s) - 1)
Exit For
End If
Next i
.Cells(cell.Row, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub

 

[shockley]

I didn't see your last post before I posted just now, but I believe the
second macro in my post does what you want--it just overwrites the result
into column 2.

Regards,
Shockley

 

[Lillian]

Shockley:

Good morning, I use the second Tester1(), it run
perfect, this is what I want, thank you so much, if I use
the first Tester1(), the original COlumn A data is gone,
it become is empty column, ColumnB is the file name I
need it, original ColumnB's data move to ColumnC, this
look good too, but the original columnA is completely
gone, any idea?

Thank you so much for the help.

You have a great day.

Lillian

 

[shockley]

Lillian,

I see the problem you are having--I hadn't considered that you might have
data in column A. The

..Columns(1).Insert

statement shifts Column A to the right (actually, it shifts all columns to
the right, which is why the pathnames end up in Column C), so Column A
becomes Column B, and Column B is where the macro later puts the file names,
so, the data that originally was in Column A is being overwritten. To
remedy this, change the

..Columns(1).Insert

statement to

..Columns(2).Insert

and you will be fine.

You have a great day too!
Shockley

 

[Lillian]

Shockley,

Yes, it work, thank you so much. your are the greatest.

Have a happy wonderfuly holiday.

Lillian