Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
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) | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
Saving files in the same network directory takes too long time!! | Setting up and Configuration of Excel | |||
How do I force an Excel macro to ask me which file and directory? | Excel Discussion (Misc queries) |