none
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.

    Monday, April 02, 2012 7:41 PM

Answers

  • 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

    • Marked as answer by Bruce Song Thursday, April 05, 2012 9:23 AM
    Monday, April 02, 2012 9:25 PM

All replies

  • 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

    • Marked as answer by Bruce Song Thursday, April 05, 2012 9:23 AM
    Monday, April 02, 2012 9:25 PM
  • 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.

    Tuesday, April 03, 2012 1:55 PM