Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 -----Original Message----- 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 "Lillian" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 -- David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 -----Original Message----- 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 "Lillian" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 -----Original Message----- 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 -----Original Message----- 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 "Lillian" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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" wrote in message ... 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 -----Original Message----- 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 -----Original Message----- 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 "Lillian" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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 -----Original Message----- 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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
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" wrote in message ... 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 -----Original Message----- 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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get file name from long directory
Shockley,
Yes, it work, thank you so much. your are the greatest. Have a happy wonderfuly holiday. Lillian -----Original Message----- 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" wrote in message ... 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 -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Takes too long for Filedialog to change directory | Excel Discussion (Misc queries) | |||
Saving files in the same network directory takes too long time!! | Setting up and Configuration of Excel | |||
Truncat the file name in long directory using macro | Excel Programming | |||
get path - save new file - same sub-directory as existing file | Excel Programming |