select PDF location through a FileDialog and remember this location

[robertvdb]

I have a sheet which contents need to be stored as a PDF file, in a certain folder.

Now I need to do two things:

1) select this folder through a FileDialog box
2) remember this folder for the future, however I still need to be able to change it's location if necessary.

Thanks in advance.

 

[frabulator]

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.

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.

 

[robertvdb]

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.

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.

Thanks Frabulator,

whereas your suggested solution is very valuable, meanwhile I've been working a bit as well, and I found a solution by storing the "SaveFolder" in a specific cell (G1) on the sheet.

This works fine but feel free to share your comments.

Private Sub cmdPDF_Click()

Dim FldrPicker As FileDialog
Dim sPDFpath As String

On Error GoTo 0

 
sPDFpath = Range("G1").Value
 
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    .InitialFileName = sPDFpath
    If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button
    sPDFpath = .SelectedItems(1) & "\"
    Range("G1").Value = sPDFpath
End With

End Sub