没有扩展名的文件名 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27923926/
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
File name without extension name VBA
提问by Liniel
I need to get file name without extension name by VBA. I know ActiveWorkbook.Nameproperty , but if user haves Windows property Hide extensions for known file typesturn off, the result of my code will be [Name.Extension]. How can I return only name of Workbook independent of windows property?
我需要通过 VBA 获取没有扩展名的文件名。我知道ActiveWorkbook.Nameproperty ，但如果用户Hide extensions for known file types关闭了Windows 属性，我的代码的结果将是 [Name.Extension]。如何仅返回独立于 Windows 属性的工作簿名称？
I try even ActiveWorkbook.Application.Captionbut I can't customize this property.
我什至尝试，ActiveWorkbook.Application.Caption但我无法自定义此属性。
采纳答案by bp_
strTestString = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
full credit: http://mariaevert.dk/vba/?p=162
回答by RubberDuck
The answers given here already may work in limited situations, but are certainly not the best way to go about it. Don't reinvent the wheel. The File System Objectin the Microsoft Scripting Runtime libraryalready has a method to do exactly this. It's called GetBaseName. It handles periods in the file name as is.
这里给出的答案可能在有限的情况下有效，但肯定不是最好的方法。不要重新发明轮子。该文件系统对象在Microsoft脚本运行时库已经做的正是这样的方法。它被称为GetBaseName。它按原样处理文件名中的句点。
Public Sub Test()
Dim fso As New Scripting.FileSystemObject
Debug.Print fso.GetBaseName(ActiveWorkbook.Name)
End Sub
Public Sub Test2()
Dim fso As New Scripting.FileSystemObject
Debug.Print fso.GetBaseName("MyFile.something.txt")
End Sub
Instructions for adding a reference to the Scripting Library
回答by Ifca
Simple but works well for me
简单但对我来说效果很好
FileName = ActiveWorkbook.Name
If InStr(FileName, ".") > 0 Then
FileName = Left(FileName, InStr(FileName, ".") - 1)
End If
回答by Bob Nightingale
Using the Split function seems more elegant than InStr and Left, in my opinion.
在我看来，使用 Split 函数似乎比 InStr 和 Left 更优雅。
Private Sub CommandButton2_Click()
Dim ThisFileName As String
Dim BaseFileName As String
Dim FileNameArray() As String
ThisFileName = ThisWorkbook.Name
FileNameArray = Split(ThisFileName, ".")
BaseFileName = FileNameArray(0)
MsgBox "Base file name is " & BaseFileName
End Sub
回答by Jeremy Smith
This gets the file type as from the last character (so avoids the problem with dots in file names)
这从最后一个字符开始获取文件类型（因此避免了文件名中的点问题）
Function getFileType(fn As String) As String
''get last instance of "." (full stop) in a filename then returns the part of the filename starting at that dot to the end
Dim strIndex As Integer
Dim x As Integer
Dim myChar As String
strIndex = Len(fn)
For x = 1 To Len(fn)
myChar = Mid(fn, strIndex, 1)
If myChar = "." Then
Exit For
End If
strIndex = strIndex - 1
Next x
getFileType = UCase(Mid(fn, strIndex, Len(fn) - x + 1))
End Function
结束函数
回答by Harry S
To be verbose it the removal of extension is demonstrated for workbooks.. which now have a variety of extensions . . a new unsaved Book1 has no ext . works the same for files
详细地说，为工作簿演示了删除扩展名......现在有各种扩展名。. 一个新的未保存 Book1 没有 ext 。对文件的作用相同
Function WorkbookIsOpen(FWNa$, Optional AnyExt As Boolean = False) As Boolean
Dim wWB As Workbook, WBNa$, PD%
FWNa = Trim(FWNa)
If FWNa <> "" Then
For Each wWB In Workbooks
WBNa = wWB.Name
If AnyExt Then
PD = InStr(WBNa, ".")
If PD > 0 Then WBNa = Left(WBNa, PD - 1)
PD = InStr(FWNa, ".")
If PD > 0 Then FWNa = Left(FWNa, PD - 1)
'
' the alternative of using split.. see commented out below
' looks neater but takes a bit longer then the pair of instr and left
' VBA does about 800,000 of these small splits/sec
' and about 20,000,000 Instr Lefts per sec
' of course if not checking for other extensions they do not matter
' and to any reasonable program
' THIS DISCUSSIONOF TIME TAKEN DOES NOT MATTER
' IN doing about doing 2000 of this routine per sec
' WBNa = Split(WBNa, ".")(0)
'FWNa = Split(FWNa, ".")(0)
End If
If WBNa = FWNa Then
WorkbookIsOpen = True
Exit Function
End If
Next wWB
End If
End Function
回答by Ahmed
Answer is here: I think this answer is good, please try it http://mariaevert.dk/vba/?p=162
答案在这里：我认为这个答案很好，请尝试 http://mariaevert.dk/vba/?p=162
