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.
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
This code was adapted from Ozgrid
and as jkf points out, from Mr Excel
Source: Stackoverflow.com