Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trancat file name using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trancat file name using VBA
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trancat file name using VBA
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 -----Original Message----- 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 -----Original Message----- 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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trancat file name using VBA
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 -----Original Message----- 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 -----Original Message----- 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 -----Original Message----- 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 . . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trancat file name using VBA
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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Lillian" wrote in message ... 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 -----Original Message----- 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 -----Original Message----- 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 -----Original Message----- 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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
I saved file A over file B. Can I get file B back? | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
I SAVED A FILE OVER ANOTHER A FILE IN EXCEL. THE OLD FILE WAS AN . | Excel Discussion (Misc queries) | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) |