vba 如何只获取文件名而不是打开文件的整个文件路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9086309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you get just the filename rather than the entire file path of an open file?
提问by stanigator
In other words, would I need to do some string processing after invoking the Application.GetOpenFileName()Method?
换句话说，我是否需要在调用Application.GetOpenFileName()方法后进行一些字符串处理？
回答by Jean-Fran?ois Corbett
Why reinvent the wheel and write tons of boilerplate code? Just use the existing FileSystemObject's GetFileName method, already written and tested and debugged for you:
为什么要重新发明轮子并编写大量样板代码？只需使用现有的FileSystemObject的 GetFileName 方法，已经为您编写、测试和调试：
filename = FSO.GetFileName(path)
Here's a working example:
这是一个工作示例：
Dim path As String
Dim filename As String
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject
path = "C:\mydir\myotherdir\myfile.txt"
filename = FSO.GetFileName(path) 'Bingo. Done.
Debug.Print filename ' returns "myfile.txt"
' Other features:
Debug.Print FSO.GetBaseName(path) ' myfile
Debug.Print FSO.GetExtensionName(path) ' txt
Debug.Print FSO.GetParentFolderName(path) ' C:\mydir\myotherdir
Debug.Print FSO.GetDriveName(path) ' C:
' et cetera, et cetera.
You will need to set a reference as follows: Tools > References... > set checkmark next to Microsoft Scripting Runtime.
您需要按如下方式设置引用：工具 > 引用... > 在 Microsoft Scripting Runtime 旁边设置复选标记。
Otherwise use late binding:
否则使用后期绑定：
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
回答by Olivier Jacot-Descombes
I am using these functions for filename processing. The last one is the one you need here.
我正在使用这些函数进行文件名处理。最后一个是你在这里需要的。
Public Function FilePathOf(ByVal s As String) As String
Dim pos As Integer
pos = InStrRev(s, "\")
If pos = 0 Then
FilePathOf = ""
Else
FilePathOf = Left$(s, pos)
End If
End Function
Public Function FileNameOf(ByVal s As String) As String
Dim pos1 As Integer, pos2 As Integer
pos1 = InStrRev(s, "\") + 1
pos2 = InStrRev(s, ".")
If pos2 = Len(s) Then pos2 = pos2 + 1
If pos2 = 0 Then pos2 = Len(s) + 1
FileNameOf = Mid$(s, pos1, pos2 - pos1)
End Function
Public Function FileExtOf(ByVal s As String) As String
Dim pos As Integer
pos = InStrRev(s, ".")
If pos = 0 Then
FileExtOf = ""
Else
FileExtOf = Mid$(s, pos + 1)
End If
End Function
Public Function FileNameExtOf(ByVal s As String) As String
FileNameExtOf = Mid$(s, InStrRev(s, "\") + 1)
End Function
回答by rich tier
activate the file in question then:
激活有问题的文件，然后：
Function getname()
arr = Split(ActiveDocument.FullName, "\")
Debug.Print arr(UBound(arr))
End Function
I assume you are using Word, hence the "ActiveDocument". Change this to "ActiveWorksheet" et al where appropriate
我假设您使用的是 Word，因此使用“ActiveDocument”。在适当的情况下将其更改为“ActiveWorksheet”等
回答by user3591272
'Simpler is Always better!! (substitute applicable cell location R1C1, and string length of path)
'简单总是更好！！（替换适用的单元格位置 R1C1 和路径的字符串长度）
Dim TheFile As String
Dim TheFileLessPath As String
Function getname()
Workbooks.Open filename:=TheFile
TheFileLessPath = Mid(TheFile, 12, 7)
ActiveCell.FormulaR1C1 = TheFileLessPath
End Function
回答by ePandit
In this case, you are using Application.GetOpenFilename(), so you are sure that file physically exists on disk, so the simplest approach will be to use Dir().
在这种情况下，您使用的是 Application.GetOpenFilename()，因此您确定该文件物理存在于磁盘上，因此最简单的方法是使用 Dir()。
fileName = Dir(filePath)
Full code is:
完整代码为：
Dim fileName, filePath As Variant
filePath = Application.GetOpenFilename("Excel files (*.xlsm), *.xlsm", , "Select desired file", , False)
If filePath = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
'Remove path from full filename
fileName = Dir(filePath)
'Print file name (with extension)
MsgBox "File selected." & vbCr & vbCr & fileName, vbInformation, "Sucess!"
End If
