vba 获取文件路径（以文件夹结尾）

Get File Path (ends with folder)

提问by Jesse Smothermon

I know how to let the user click on a button to navigate to a specific file to open.

我知道如何让用户单击按钮导航到要打开的特定文件。

Code:

代码：

Private Sub CommandButton2_Click()
    Dim vaFiles As Variant

    vaFiles = Application.GetOpenFilename()

    ActiveSheet.Range("B9") = vaFiles
End Sub

I want a second button that will let the user navigate to a folder to save the .pdffile that my program creates.

我想要第二个按钮，让用户导航到文件夹以保存.pdf我的程序创建的文件。

The problem: The GetOpenFilenamerequires the user to click on a file. If there's no file in the folder then there's nothing the user can do.

问题：GetOpenFilename需要用户点击一个文件。如果文件夹中没有文件，则用户无能为力。

回答by chris neilsen

Use the Application.FileDialogobject

使用Application.FileDialog对象

Sub SelectFolder()
    Dim diaFolder As FileDialog
    Dim selected As Boolean

    ' Open the file dialog
    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    selected = diaFolder.Show

    If selected Then
        MsgBox diaFolder.SelectedItems(1)
    End If

    Set diaFolder = Nothing
End Sub

回答by Derek

Have added ErrorHandler to this in case the user hits the cancel button instead of selecting a folder. So instead of getting a horrible error message you get a message that a folder must be selected and then the routine ends. Below code also stores the folder path in a range name (Which is just linked to cell A1 on a sheet).

已添加 ErrorHandler 以防用户点击取消按钮而不是选择文件夹。因此，您不会收到可怕的错误消息，而是收到一条消息，必须选择文件夹，然后例程结束。下面的代码还将文件夹路径存储在一个范围名称中（它只是链接到工作表上的单元格 A1）。

Sub SelectFolder()

Dim diaFolder As FileDialog

'Open the file dialog
On Error GoTo ErrorHandler
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Title = "Select a folder then hit OK"
diaFolder.Show
Range("IC_Files_Path").Value = diaFolder.SelectedItems(1)
Set diaFolder = Nothing
Exit Sub

ErrorHandler:
Msg = "No folder selected, you must select a folder for program to run"
Style = vbError
Title = "Need to Select Folder"
Response = MsgBox(Msg, Style, Title)

End Sub

回答by ray

In the VBA Editor's Tools menu, click References... scroll down to "Microsoft Shell Controls And Automation" and choose it.

在 VBA 编辑器的工具菜单中，单击引用...向下滚动到“Microsoft Shell 控件和自动化”并选择它。

Sub FolderSelection()
    Dim MyPath As String
    MyPath = SelectFolder("Select Folder", "")
    If Len(MyPath) Then
        MsgBox MyPath
    Else
        MsgBox "Cancel was pressed"
    End If
End Sub

'Both arguements are optional. The first is the dialog caption and
'the second is is to specify the top-most visible folder in the
'hierarchy. The default is "My Computer."

Function SelectFolder(Optional Title As String, Optional TopFolder _
                         As String) As String
    Dim objShell As New Shell32.Shell
    Dim objFolder As Shell32.Folder

'If you use 16384 instead of 1 on the next line,
'files are also displayed
    Set objFolder = objShell.BrowseForFolder _
                            (0, Title, 1, TopFolder)
    If Not objFolder Is Nothing Then
        SelectFolder = objFolder.Items.Item.Path
    End If
End Function

Source Link.

源链接。

回答by jonsca

Use Application.GetSaveAsFilename()in the same way that you used Application.GetOpenFilename()

用Application.GetSaveAsFilename()同样的方式，你使用 Application.GetOpenFilename()

回答by Bhagwat Singh

This might help you out:

这可能会帮助你：

Sub SelectFolder()
    Dim diaFolder As FileDialog
    Dim Fname As String

    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    diaFolder.Show

    Fname = diaFolder.SelectedItems(1)

    ActiveSheet.Range("B9") = Fname

End Sub

回答by Lah Ezcen

If you want to browse to a folder by default: For example "D:\Default_Folder" just initialise the "InitialFileName" attribute

如果要默认浏览到文件夹：例如“D:\Default_Folder”只需初始化“InitialFileName”属性

Dim diaFolder As FileDialog

' Open the file dialog
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.InitialFileName = "D:\Default_Folder"
diaFolder.Show