Change Export Query as txt, csv, xls, or dbf to open SaveAs Dialog box

[BMeek]

I have the following code behind a form in an Access 2000 program that allows a user to select a file type from a set of option buttons then click a command button that automatically exports the file using DoCmd.TransferSpreadsheet. I have code that tries to save the file into the default "My Documents" folder, but some of our user's have networked PCs that have a network drive as their default "save as" area. What I want to do is simply open the "SaveAs Dialog" box and let the user save the query as a file any place they like as well as write their own file name.

How can I adjust the code to accomplish that?

Private Sub cmdExport_Click()
Dim IndividualControl As Control
Dim OutputDir As String
Dim SqlString As String
Dim CheckBox As CheckBox
Dim qdHidden As DAO.QueryDef

'This section allows the user to select the fields to export
For Each IndividualControl In Me.Controls
If IndividualControl.Name Like "chk*" Then
Set CheckBox = IndividualControl
If CheckBox.Value Then
SqlString = SqlString + ", " + Mid(CheckBox.Name, 4)
End If
End If
Next IndividualControl
SqlString = Mid(SqlString, 3)

'This creates the copy of the base query using only the selected fields
Set qdHidden = CurrentDb.QueryDefs("_hidden1")
qdHidden.SQL = "select " + SqlString + " from qryOutputFollwupAgents;"

'This section looks for the default file path
If Environ("HOMEPATH") <> "" Then
OutputDir = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\my documents\"
Else
OutputDir = Environ("TEMP")
End If

'This section outputs the file based on the option button selection
Select Case frmChooseExportFileType
Case 1 ' Excel
DoCmd.TransferSpreadsheet acExport, 8, "_hidden1", OutputDir + "AgtFollowup.xls", -1
Case 2 ' Text
DoCmd.TransferText acExportDelim, , "_hidden1", OutputDir + "AgtFollowup.txt", -1
Case 3 ' CSV
DoCmd.TransferText acExportDelim, , "_hidden1", OutputDir + "AgtFollowup.csv", -1
Case 4 ' DBF IV
DoCmd.TransferDatabase acExport, "dbase III", OutputDir, acQuery, "_hidden1", "AgtFollowup.dbf"
End Select
'Display the path where the file was saved
MsgBox "File export to " & OutputDir & " folder completed.", vbOKOnly, "File Export"
End Sub

Bryan Meek
bmeek@adelphia.net

 

[PHV]

Depending of your version of office you may consider the FileDialog object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BMeek]

Hi PVH,
Thanks for the suggestion. While the FileDialog box is easily found in the 2003 version of Access, what can I use in it's place for Access 2000?


Bryan Meek