Change Default File Save As Folder with VBA

Question

Access 2010. With report print preview, you can select Save & Publish, Save Object As, PDF or XPS, then click on Save As.

This opens a dialog set to folder Libraries, Documents.  I would like to change this location using VBA within the Access app.

Note that this doesn't seem to be the SetOptions Default Database Folder. It seems to be a Windows Explorer default.

Opening Windows Explorer and setting the properties there is not a choice; this is software for an end user and I would like to set this default from within Access.

Answer 1

I don't see how you could change this while users are using the SaveAs commands on the built-in ribbon.

However if you wrote your own code for the user to save the report as pdf or xps, then you could use code to open Windows Explorer at the folder you want.

Here is some code I have used for years to open Windows explorer.

It uses the well known FileOpen code which you can get here:

http://access.mvps.org/access/api/api0001.htm

It also uses a function called fGetSpecialFolderLocation, which you can get here:

http://access.mvps.org/access/api/api0054.htm

------------------------
Public Function fctGetSavePath(strFileName As String, _
                                Optional varDirectory As Variant, _
                                Optional varTitleForDialog As Variant) As Variant
    'modified  12/4/06
On Error GoTo FunctionErr

    Dim strFilter As String
    Dim strSaveFileName As String   'name to save file as
    Dim strPath As String   'new file save as name
    Dim lngFlags As Long

    ' don't bother displaying the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_HIDEREADONLY
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If
    'Open a file save dialog box for xls files
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
    'set the initial directory to the desktop
    'set the text for the title of the FileOpen dialog box
    'Specify the default file name
    'strFileName = Me.txtDefaultFileName, supplied to function from calling form
    'Specify the default file extension
    'set openfile = false (for save instead of open)
    'Now actually call to get the file and path.
    fctGetSavePath = ahtCommonFileOpenSave( _
                  Filter:=strFilter, _
                  OpenFile:=False, _
                  InitialDir:=fGetSpecialFolderLocation(&H10), _
                  DefaultExt:="xls", _
                  FileName:=strFileName, _
                  DialogTitle:="Save file", _
                  Flags:=ahtOFN_HIDEREADONLY)
                  'Debug.Print fctGetSavePath

FunctionExit:
    Exit Function
FunctionErr:
    MsgBox Err.Number & " " & Err.Description
    Resume FunctionExit

End Function

------------------------------------

---

Jeanette Cunningham

Answer 2

Thanks Jeannette. Haven't used the fGetSpecialFolderLocation before, so that should be especially helpful.

Agree with your solution. Too bad there doesn't seem to be a simple variable to set! Regards.