0

I have a code I have pieced together. The goal is to take all the excel files in a folder, open them up and add the sheets together to form a pivot table. I can easily set a path = 'foldername' and it works, however, I was wanting to make it to where I could just browse for the folder instead and have the path reference the chosen path from the msoFileDialogFolderPicker. I know that the path=Diafolder is incorrect and am looking for an answer to this. Thank you ahead of time.

Dim Diafolder As FileDialog
Dim path As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

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

Msgbox Diafolder.SelectedItems(1)

Set Diafolder = Nothing
path = Diafolder

fileName = Dir(path & "*.xls")

Do While fileName <> ""
J Doncouse Threedogbowwow
  • 1
  • 1
  • This has an answer here https://stackoverflow.com/questions/26392482/vba-excel-to-prompt-user-response-to-select-folder-and-return-the-path-as-string?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Apr 11 '18 at 19:55

1 Answers1

0

This is the way you can do it:

Set Diafolder = Application.FileDialog(msoFileDialogFolderPicker)
With Diafolder 

    .AllowMultiSelect = False
    .Title = "Select a folder..."

    If .Show = True Then
        path = .SelectedItems(1)
    End If

End With
drec4s
  • 7,946
  • 8
  • 33
  • 54
  • Thank you so much!. I kinda got it working. The only issue I believe I'm running into is that I'm noticing that when the folder is chosen it isn't adding the final "\" to the path so none of the files in the folder gets opened. For some strange reason if I select the folder and hit cancel it actually works correctly "granted I have to be inside the folder in the dialog box" – J Doncouse Threedogbowwow Apr 13 '18 at 13:07
  • Well you can add the trailing "\" by doing `path = .SelectedItems(1) & "\"` – drec4s Apr 13 '18 at 13:09
  • Thank you so much. I ended up figuring it out looking up some other issues I was having. – J Doncouse Threedogbowwow Apr 16 '18 at 19:03
  • Glad to help. Please consider marking the answer as correct. – drec4s Apr 16 '18 at 19:38