Raj,

As I understand it you want to let the user select a folder in which to
save the exported file, rather than specify a file name. For that you
need to use Application.FileDialog(msoFiledialogFolderPicker). In any
case, according to
http://support.microsoft.com/default.aspx?scid=kb;EN-US;282335 you
cannot use msoFileDialogSaveAs in Access 2002 (I don't know whether the
problem has been fixed in Access 2003). The other problem in your code
is that you didn't do anything to pass the selected name from the dialog
to TransferSpreadsheet. What you need is something like the code below.

Private Sub cmdExport_Click()

 Const FILENAME = "XXX.xls"
 Const INITIAL_FOLDER = "C:\Folder\Subfolder"
 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, _
     "MainData", strFileName, True
   
Exit_cmdExport_Click:
 Set dlgD = Nothing
 Exit Sub

Err_cmdExport_Click:
 MsgBox Err.Description
 Resume Exit_cmdExport_Click
End Sub

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.