none
DoCmd.OutputTo ... How do I get the file dialog to use the default folder instead of the previous folder RRS feed

  • Question

  • I am using the following code in Access 2010, which is launched from a custom Ribbon button:

        DoCmd.OutputTo acOutputForm, Screen.ActiveForm.Name, acFormatXLSX

    If I use the built-in Email function ("E-mail" button on Ribbon), where it attaches a form or report to an email ... then the next time I run the code above, the default folder in the file dialog becomes: "C:\Program Files (x86)\Microsoft Office\Office14", instead of the default folder of the client app.  I assume the E-mail function is causing this.

    Is there any way to get Access to 'forget' the previous destination folder, or tell it to use the default folder for the file dialog each time I run this code?

    Friday, October 6, 2017 4:25 PM

Answers

  • You can use the ChDir command before running your DoCmd.OutputTo VBA line.

    • Marked as answer by TraciMarie Friday, October 6, 2017 6:31 PM
    Friday, October 6, 2017 4:56 PM
  • You can change the current directory with ChDir(). Let's say you want to have the folder the database is in show up.

        ChDir CurrentProject.Path
        Debug.Print CurDir
    

    or if you want to specify a folder

        ChDir "C:\MyFolder"
        Debug.Print CurDir
    


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by TraciMarie Friday, October 6, 2017 6:31 PM
    Friday, October 6, 2017 5:34 PM

All replies

  • Hi Traci,

    When you click the Custom Ribbon button, what is the first default location it shows? Then, when you click on the Email button, does it show the same or last location used from the OutputTo code?

    If so, maybe it's built-in to Windows where it remembers the last location in the Registry or somewhere, so it can offer the same location for potential convenience.

    To change this behavior, using Access, I can think of two possible approaches.

    1. Find out where the last location is stored and modify it, or

    2. Change your code to ask the user to browse to a file location and filename using the FileDialog object before executing the OutputTo command.

    Hope it helps...

    Friday, October 6, 2017 4:47 PM
  • You can use the ChDir command before running your DoCmd.OutputTo VBA line.

    • Marked as answer by TraciMarie Friday, October 6, 2017 6:31 PM
    Friday, October 6, 2017 4:56 PM
  • You can change the current directory with ChDir(). Let's say you want to have the folder the database is in show up.

        ChDir CurrentProject.Path
        Debug.Print CurDir
    

    or if you want to specify a folder

        ChDir "C:\MyFolder"
        Debug.Print CurDir
    


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by TraciMarie Friday, October 6, 2017 6:31 PM
    Friday, October 6, 2017 5:34 PM
  • Lawrence and Bill,

    Thanks so much!  That was an easy fix.  Works like a charm. :)

    Here is my final code.  I found the 'GetOption' line on another board, where you could get the default directory from options, but nowhere did it explain how to set it!

        Dim strDefaultDB As String
        strDefaultDB = GetOption("Default Database Directory")   
        ChDir strDefaultDB
        DoCmd.OutputTo acOutputForm, Screen.ActiveForm.Name, acFormatXLSX

    DBGuy ... To answer your questions, it normally just defaults to whatever the default directory is in options (Default database folder).  Clicking the Email button doesn't show a location (no dialog appears).  It simply generates a new email and attaches form to it.

    Friday, October 6, 2017 6:04 PM
  • Cool trick guys. Cheers!
    Friday, October 6, 2017 6:38 PM