Newbie - Export to Excel Problem

[JShumaker]

I use Access 2000 and fairly new to VB. I've set up a
parameter query which the user can enter information to
extract only certain records to export. Once the query is
run the results can be seen in a continuous form called
frmExport. I would like to set up a button for the user
to export the results shown in the form to a spreadsheet
in Excel and let the user select where they want to save
the file. I found some sample code from John Nurick dated
8/28/04 and attempted to use it. However, I too keep
getting the error code: "user defined type not defined".
The code stops at: Dim dlgD As Office.FileDialog. Is
there something else I need to add for Access 2000 in
order for the code to work? Appreciate any help you can
give.

Here's what I have so far:

Private Sub cmdExport_Click()
Const FILENAME = "ExportData.xls"
Const INITIAL_FOLDER = "C:\"
Dim strFileName As String
Dim dlgD As Office.FileDialog

On Error GoTo Err_cmdExport_Click

Set dlgD = Application.FileDialog
(msoFileDialogFolderPicker)
With dlgD
.Title = "Select location to save file"
.InitialFileName = INITIAL_FOLDER
.Show
If .SelectedItems.Count = 0 Then
MsgBox "User clicked Cancel", vbInformation +
vbOKOnly
GoTo Exit_cmdExport_Click:
End If
strFileName = .SelectedItems(1) & "\" & FILENAME
End With
MsgBox "About to save to " & strFileName, _
vbInformation + vbOKOnly
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
"ExportData", strFileName, True

Exit_cmdExport_Click:
Set dlgD = Nothing
Exit Sub

Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click

End Sub

 

[John Nurick]

As far as I can remember the FileDialog object does not exist in Access
2000.

For a solution that works in all versions from at least 97 onwards,
download the code at http://www.mvps.org/access/api/api0002.htm and use
that to invoke the Browse for Folder dialog. (If you want the user to be
able to type a filename too, use the code at
http://www.mvps.org/access/api/api0001.htm to get the standard File Save
dialog.)