Okay, this is a bit complex, but works like a charm. It is actually easier
than some other ways and presents a very professional appearance to your app.
First, go to this site and copy the code.
http://www.mvps.org/access/api/api0001.htm
Now, paste the code into a new module by itself. I named mine
modCommonDialog.
It will expose the file dialog box you see in a lot of Windows apps.
Now, to use it, you will want to use the following code as an example. It
will show only .xls files in the directory.
Dim lngFlags As Long
Dim strFilter As String
Dim strSaveFile As String
Const conExt As String = "xls"
Const conTitle As String = "Score Card Report"
On Error GoTo cmdOkay_Click_Error
If Nz(Me.OpenArgs, 2) = 3 Then 'Excel Export
lngFlags = ahtOFN_OVERWRITEPROMPT
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.xls")
strSaveFile = ahtCommonFileOpenSave( _
Flags:=lngFlags, _
Filter:=strFilter, _
DefaultExt:=conExt, _
DialogTitle:=conTitle, _
OpenFile:=False, _
Filename:="Score Card Report " & Me.txtRptYear)
If strSaveFile = vbNullString Then
MsgBox "Report Canceled", vbInformation, "Stars"
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qselRptScoreCardXL", strSaveFile, True, "ScoreCard " &
Me.txtRptYear
Call fHandleFile(strSaveFile, WIN_NORMAL)
End If
There will be some differences that you will want to change to meet your
needs.
Since you know the directory, you can pass it to the function in the
InitialDir argument. It will then open to that directory
OpenFile:=False
Change it to True
Const conTitle As String = "Score Card Report"
Change this to whatever you want. It will be the title in the dialog
You will also have to modify the TransferSpreadsheet code to suit your app.
