setting the export location through code or macro

  • Thread starter Thread starter mpfohl
  • Start date Start date
M

mpfohl

I want to create a button to export a query to an excel file.

Ideally, I'd like to just prompt the user for a name and location for
the exported file, but I'm not sure how easy that will be. I don't
want to force the user to type in the file path name, i'd rather them
go through the folders/subfolders with the normal dialog box.

So failing that, I'd settle for the excel file simply being saved to
the same directory the database lives in. However, as this database
will be used as a template and shared, that directory will change.
Thus, I would need to first find the location of the database, and then
set that as the export location. I think I may have to use the
GetOption("Default Database Directory") method, but not sure how to do
it.

Any ideas on how to pull this off?

Thanks
 
Look for help on FileDialog. You can prompt the user to select a folder and
type in a filename. The same as if you created a new file in Word then
click Save As.

NOTE: Need Access 2003 (Access 2002 may have had but 2000 definitely did
not).
 
Here is a site where you can get code that will present a Common Dialog box
where you can capture either open or save filenames and paths. Here is an
example of how to use the code:

'Set up the Default path and file
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
strFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import Adjusted Actuals")
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
Else
DoCmd.TransferSpreadsheet acExport, , "QueryNameHere", _
varGetFileName, True
End If


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

Once you have captured the path to a string variable, use it as the File
argument in the TransferSpreadsheet method.
 
Back
Top