If you only need it to be remembered while Excel is opened (as in, on a 'per use' basis), you can declare the location as a shared string above the executed code.
VBA Code:
Dim SaveFolder As String
Sub GetAndSaveFolder()
If SaveFolder = "" Then
SaveFolder = GetFolder
Else
Dim rs As Variant
rs = MsgBox("There is already a save folder selected. Would you like to pick a new save location?", vbYesNo)
If rs = vbYes Then SaveFolder = GetFolder
End If
End Sub
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
If you want Excel to remember the location forever, or until it is overwritten, you will need to save that string somewhere on the computer. This can be one as a txt file or in the registry, though saving the file somewhere does require access to that location and can trigger virus scan protection.