vba 如何获取使用文件对话框打开的文件的选定路径和名称？

How to get selected path and name of the file opened with file dialog?

提问by VeVi

I need the path name and file name of the file that is opened with File Dialog. I want to show this information with a hyperlink in my worksheet.

我需要用文件对话框打开的文件的路径名和文件名。我想在我的工作表中使用超链接显示此信息。

With this code I have the file path:

使用此代码，我有文件路径：

Sub GetFilePath()

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
    If .Show <> -1 Then
        Exit Sub
    End If
FileSelected = .SelectedItems(1)
End With

ActiveSheet.Range("A1") = FileSelected
End Sub

I'm still looking for a way to get the filename.

我仍在寻找获取文件名的方法。

回答by chris neilsen

Try this

尝试这个

Sub Demo()
    Dim lngCount As Long
    Dim cl As Range

    Set cl = ActiveCell
    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show
        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            ' Add Hyperlinks
            cl.Worksheet.Hyperlinks.Add _
                Anchor:=cl, Address:=.SelectedItems(lngCount), _
                TextToDisplay:=.SelectedItems(lngCount)
            ' Add file name
            'cl.Offset(0, 1) = _
            '    Mid(.SelectedItems(lngCount), InStrRev(.SelectedItems(lngCount), "\") + 1)
            ' Add file as formula
            cl.Offset(0, 1).FormulaR1C1 = _
                 "=TRIM(RIGHT(SUBSTITUTE(RC[-1],""\"",REPT("" "",99)),99))"


            Set cl = cl.Offset(1, 0)
        Next lngCount
    End With
End Sub

回答by meticoeus

You can get any part of the file path using the FileSystemObject. GetFileName(filepath) gives you what you want.

您可以使用 FileSystemObject 获取文件路径的任何部分。GetFileName(filepath) 给你你想要的。

Modified code below:

修改后的代码如下：

Sub GetFilePath()
Dim objFSO as New FileSystemObject

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FileSelected = .SelectedItems(1)
End With

ActiveSheet.Range("A1") = FileSelected 'The file path
ActiveSheet.Range("A2") = objFSO.GetFileName(FileSelected) 'The file name
End Sub

回答by Fabio Reche

To extract only the filename from the path, you can do the following:

要仅从路径中提取文件名，您可以执行以下操作：

varFileName = Mid(fDialog.SelectedItems(1), InStrRev(fDialog.SelectedItems(1), "\") + 1, Len(fDialog.SelectedItems(1)))

回答by JMK

I think you want this:

我想你想要这个：

Dim filename As String
filename = Application.GetOpenFilename

Dim cell As Range
cell = Application.Range("A1")
cell.Value = filename

回答by user1527032

I think this is the simplest way to get to what you want.

我认为这是获得所需内容的最简单方法。

Credit to JMK's answer for the first part, and the hyperlink part was adapted from http://msdn.microsoft.com/en-us/library/office/ff822490(v=office.15).aspx

归功于 JMK 对第一部分的回答，超链接部分改编自http://msdn.microsoft.com/en-us/library/office/ff822490(v=office.15).aspx

'Gets the entire path to the file including the filename using the open file dialog
Dim filename As String
filename = Application.GetOpenFilename

'Adds a hyperlink to cell b5 in the currently active sheet
With ActiveSheet
 .Hyperlinks.Add Anchor:=.Range("b5"), _
 Address:=filename, _
 ScreenTip:="The screenTIP", _
 TextToDisplay:=filename
End With

回答by Akshat Agrawal

The below command is enough to get the path of the file from a dialog box -

以下命令足以从对话框中获取文件的路径 -

my_FileName = Application.GetOpenFilename("Excel Files (*.tsv), *.txt")

回答by Ace1000

FileNameOnly = Dir(.SelectedItems(1))

FileNameOnly = Dir(.SelectedItems(1))

回答by Tanmay Nehete

I think this will do:

我认为这会做：

Dim filename As String
filename = Application.GetOpenFilename

回答by Chikku Jacob

From office 2010, we won't be able to use the common dialog box control, so it's nice to use the Application object to get the desired results.

从 Office 2010 开始，我们将无法使用通用对话框控件，因此最好使用 Application 对象来获得所需的结果。

Here I got a text box and Command button - paste the following code under the command button click event, which will open the file dialog box and add the File name to the Text box.

这里我得到了一个文本框和命令按钮——在命令按钮单击事件下粘贴以下代码，这将打开文件对话框并将文件名添加到文本框。

Dim sFileName  As String

sFileName = Application.GetOpenFilename("MS Excel (*.xlsx), *.xls")

TextBox1.Text = sFileName

回答by RAJESH BAWKAR

The code starts file search from root colon, If I want to start search from a specific directory, to avoid going to that directory every time, where I should put one. I did it like

代码从根冒号开始文件搜索，如果我想从特定目录开始搜索，以避免每次都去那个目录，我应该放一个。我这样做了

Sub GetFilePath()
FileSelected = "G:\Audits\A2010"
Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FileSelected = .SelectedItems(1)
End With

ActiveSheet.Range("C14") = FileSelected
End Sub

But it could not start reach from "G:\Audits\A2010"

但它无法从“G:\Audits\A2010”开始