Importing folder to Excel (FileDialogFolderPicker) using VBA

Question

I'm using the next code in order to select a folder from a certain path and import all the files inside it:

Function GetFolder()
  Dim fd As FileDialog
  Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  fd.Title = "Select Excel Workbook(s) Folder"
  Dim vrtSelectedItem As Variant

  With fd
    If .Show = -1 Then
      For Each vrtSelectedItem In .SelectedItems
        GetFolder = vrtSelectedItem
      Next vrtSelectedItem
    Else
    End If

  End With
  Set fd = Nothing


End Function

When the Folder Picker window opens it start on the desktop. Is there a way to make it go to a specific path upon opening? or open where the excel file itself is located?

Answer 1

You would update the InitialFileName property, and you could set it to use the ActiveWorkbook.Path You'll need to make sure that you include the ending slash, or it will only display the previous folder instead of the folder you want. Also, there is no reason to loop through the .SelectedItems collection because the FolderPicker FileDialog doesn't support mutliple selections.

In summary, I think this is the code you're looking for:

Function GetFolder()

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ActiveWorkbook.Path & Application.PathSeparator
        .Title = "Select Excel Workbook(s) Folder"
        If .Show = True Then
            GetFolder = .SelectedItems(1)
        Else
            GetFolder = False
        End If
    End With

End Function

Answer 2

add a line like this before .Show:

 fd.InitialFileName = "c:\whateverInitialDirectory"