Excel VBA：使用 getOpenFilename 打开文件夹和文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13011827/
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
Excel VBA: Use getOpenFilename to open folder AND files
提问by vince_h
I want to use this routine Application.GetOpenFilenameto open either a *.txtfile OR a whole folder. Is this somehow possible?
E.g. if no file/folder is selected, the parent's folder path is returned, otherwise the selected filename?
我想使用此例程Application.GetOpenFilename打开*.txt文件或整个文件夹。这有可能吗？
例如，如果没有选择文件/文件夹，则返回父文件夹路径，否则选择文件名？
Example: Let's assume I have a file called "test.txt"in the path C:\folder1\folder2\test.txt. Now I am lazy when searching files and select C:\folder1(the "parent folder"). My program now searches within the subfolders for test.txt. But sometimes I am not lazy and I want to select the specific file test.txt
示例：假设我有一个"test.txt"在 path 中调用的文件C:\folder1\folder2\test.txt。现在我在搜索文件和选择C:\folder1（“父文件夹”）时很懒惰。我的程序现在在子文件夹中搜索test.txt. 但有时我不懒惰，我想选择特定文件test.txt
I am searching for one user friendly dialog to handle both: open a folder (and return the folder path only) and open a file (and return the file path)
我正在寻找一个用户友好的对话框来处理两者：打开一个文件夹（并仅返回文件夹路径）和打开一个文件（并返回文件路径）
回答by Scott Holtzman
By parentI assume you mean the file from which the VBA is called. If not you should be able to adjust the below pretty easilly.
通过parent我假定你的意思是从哪个VBA正在调用该文件。如果没有，您应该能够轻松调整以下内容。
Sub getFileorFolder()
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen = False Then fileToOpen = ThisWorkbook.Path
MsgBox "File is " & fileToOpen
End Sub
回答by DeerSpotter
I have a better way of opening text files, but utilizing one of the answers above.
我有更好的打开文本文件的方法，但使用上述答案之一。
Sub ImportTextFile()
'better method to retrieving Data from txt.
If Not Range("A2").Value = "" Then
MsgBox "Clear Data First"
Sheets("Input DATA").Select
Exit Sub
End If
fileToOpen = application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen = False Then fileToOpen = ThisWorkbook.Path
MsgBox "File is " & fileToOpen
With ActiveSheet.QueryTables.Add(connection:= _
"TEXT;" + fileToOpen, Destination:=Range("$A"))
'.name = "All"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Call RemoveEmptyRows
End Sub
Sub RemoveEmptyRows()
On Error Resume Next
Range("A2:A5000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Resume:
Range("A2").Select
End Sub
