21

I am trying to write a VBA code where a dialog box would appear for the user to select where they want to save the files. However, I just need the path value (e.g. c:\Desktop\Values) returned as a string variable so that I could use it in another function. Any help would be appreciated.

Joe Shaw
  • 22,066
  • 16
  • 70
  • 92
Eric
  • 255
  • 1
  • 2
  • 6
  • 2
    http://stackoverflow.com/questions/19372319/vba-folder-picker-set-where-to-start – Alter Oct 15 '14 at 21:37
  • I want the full path name. Doesn't that just return Folder name? – Eric Oct 15 '14 at 21:47
  • no, you can get the full path (see Gary's answer) – Alter Oct 15 '14 at 22:09
  • 1
    In the answer that I posted in the link given above, if you add `Msgbox Ret` after `Ret = BrowseForFolder("C:\")` then you will get the folder path as string provided the user didn't press `Cancel` – Siddharth Rout Oct 15 '14 at 22:57

1 Answers1

62

Consider:

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

and as jkf points out, from Mr Excel

DougthePug
  • 13
  • 4
Gary's Student
  • 95,722
  • 10
  • 59
  • 99