Insert control for user to choose save location

[spacebouncer]

Hi. My program's creating some sheets and saving as pdfs. Happy with that part. I'd like to insert a control to allow the user to set the save location. Is it possible to insert a control like this? Hoping I could get one that works in the usual Microsoft way to allow them to easily choose a folder to save to. Thanks!

 

[John_w]

I don't know if there is a control, but the VBA would be based on Application.FileDialog (msoFileDialogFolderPicker).

 

[spacebouncer]

Thanks John. I'll look in to this and let you know how it goes.

 

[spacebouncer]

Nice and easy! Thanks a lot John. SelectedItems property is exactly what I was after!

FileDialog Members (Office)

 

[mikerickson]

You could try either this, which actualy saves the Active Workbook in the specified location

If Application.Dialogs(xlDialogSaveAs).Show Then
    MsgBox "saved"
Else
    MsgBox "cancled"
End If

or you could use this, which returns a file path, that you would use in an explicit .Save statement

Dim uiFilePath As String

uiFilePath = Application.GetSaveAsFilename
If uiFilePath = "False" Then
    MsgBox "canceled"
Else
    MsgBox "save to " & uiFilePath
End If

 

[spacebouncer]

Hi Mike. Thanks.

Its the chosen path that I'll want to use later so I'll go with something like below, and write the path into a textbox. For each pdf produced from the data I'll save using this path. Thanks both, it would have taken me a long time to find this.

Sub ChooseSaveFolder()

With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ActiveSheet.TextBox1.Value = .SelectedItems(1)
End With

End Sub