Folder Picker

[mo2783]

I am trying to create a dialog box that will select a path to a folder which i will then use to open a series of excel files.

I have the following code, but unable to return the path from the function, can anyone help me please?

Public Function cmdSelectInput() As String
    
    Dim strFolderPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Boxplot Location Directory"
        .ButtonName = "Open"
        .Show
    End With
  
End Function

Thanks

Mo

 

[HarleyQuinn]

How about something like this?:

Public Function cmdSelectInput() As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Boxplot Location Directory"
        .ButtonName = "Open"
        .Show
        [red]cmdSelectInput = .SelectedItems.Item(1)[/red]
    End With
  
End Function

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

MO,

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Boxplot Location Directory"
        .ButtonName = "Open"
        .Show[b]
        cmdSelectInput = .SelectedItems(1)[/b]

    End With

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[RivetHed]

Hi,

I think setting cmdSelectInput to CurDir at the end should get the function to return the selected folder.

 

[mo2783]

Thanks guys, that work a treat, just had to add \ to the end.

Anyway here is the complete function.

Public Function cmdSelectInput() As String
    
    Dim strFolderPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Boxplot Location Directory"
        .ButtonName = "Open"
        .Show
        cmdSelectInput = .SelectedItems.Item(1) & "\"
    End With
    
End Function
[\code]

 

[fumei]

Or, the one PHV posted a while back. This one displays a dialog starting from a passed starting folder - e.g. "C:\", or "c:\Yadda\", and returns the selected folder:

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", _
         16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

the second displays the folder dialog with no passed initial folder, so it starts with the system default, usually MyDocuments.

Function PickFolder2() As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", _
         16 + 32 + 64)
If (Not f Is Nothing) Then
  PickFolder2 = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

faq219-2884

Gerry
My paintings and sculpture