OutputTo default directory (1 Viewer)

[helenlou]

Is there a way I can save a report to file (pref using the OutPutTo method), prompting the user for a filename and directory location but displaying the file save dialog box with a default location. The users will mostly be saving files to the same location and it would be nice to avoid them having to change the directory.

Thanks

H

 

[ghudson]

Check out this code to open the "Browse Folder"...
http://www.mvps.org/access/api/api0002.htm

I would pass the users folder selection to a text box and use another text box for the user to name their file output.

Combine the two text boxes for your output file location.

HTH

 

[helenlou]

Thanks for the link but is it possible to get the folders window to default to a file location other than My Computer? Otherwise I am no better off than leaving the filename in the Output method blank which automatically prompts for location and filename.

I had a look at the code but I'm not a VB expert.

Any ideas?

H

 

[ghudson]

Do you want the browser to default to a specific directory yet the user can still choose another folder?

Or do you want the files to be saved to one specific folder for all users, all the time?

 

[partizan82]

This is what i've used...

Dim ExcelApp As Excel.Application
Dim SaveFile As String
Set ExcelApp = CreateObject("Excel.Application")
SaveFile = ExcelApp.GetSaveAsFilename("LT_Report", "Excel Workbooks (*.xls), *.xls")
If SaveFile <> "False" Then
DoCmd.OutputTo acOutputQuery, "LeadTime", acFormatXLS, SaveFile, False
ExcelApp.Visible = True
ExcelApp.Workbooks.Open SaveFile
ExcelApp.Cells.Select
ExcelApp.Selection.Rows.AutoFit
ExcelApp.Selection.Columns.AutoFit
ExcelApp.Range("A1").Select
ExcelApp.ActiveWorkbook.Save
End If
Set ExcelApp = Nothing

the only thing you have to do is to add excel library to access (Tools --> References --> ....). And from that point the default directory for a user will be the one he uses in excel... so he can set it up the way he/she wants...

Regards,
Anton

 

[helenlou]

I am saving a report as an .rtf file so the Excel solution isn't a goer. I want the dialog box to default to a parent directory but the user can then choose any directory beneath it or above it as they please.

Thanks for the help!

H

 

[partizan82]

Helen,

You can save it in any format you want... all this [SaveFile = ExcelApp.GetSaveAsFilename("LT_Report", "Excel Workbooks (*.xls), *.xls")] does is getts the destination of the file and it's name and gives it default extantion... you can put your extantions to "Rich Text Format (*.rtf) instead of .xls, it will work just as well...

Regards,
Anton

 

[petehume]

A little late but...

The probelm with this is that it is a local setting in the Access "Options" menu. The good news is, it's still editable.

    Dim strOutputDir As String
    Dim strDefaultDB As String

    'Get current value of default directory, so that you can reset it after
    'you are finished
    strDefaultDB = GetOption("Default Database Directory")

    'Set the output directory to YOUR_DIRECTORY_HERE
    strOutputDir = YOUR_DIRECTORY_HERE
    SetOption "Default Database Directory", strOutputDir
    
    'Output report as .RTF file and automatically open word to view
    'This does not have to be output to Word, it is just the example 
    'in my case
    DoCmd.OutputTo acOutputReport, , acFormatRTF, , -1
   
    'Set the output directory back to its original value
    SetOption "Default Database Directory", strDefaultDB