I recently had occasion to want to export the results of a query from Access to Excel using FileDialog. I found researching it that there was a lot of confusion regarding whether SaveAs could even be used in Access and couldn't find a simple routine. I experimented a little and came up with the following which works perfectly.
Although I used this routine to export to Excel it could in theory be used with any format supported by DoCmd.Output or really any statement which output a file.
-
Private Sub cmdSendtoExcel_Click()
-
Dim fd As FileDialog
-
Dim Title As String
-
Dim vrtSelectedItem As Variant
-
-
Set fd = Application.FileDialog(msoFileDialogSaveAs)
-
-
With fd
-
.AllowMultiSelect = False
-
.Title = "Save File"
-
.InitialFileName = "Name Of Report " & Format(Now(), "ddmmyyyyhhnn") & ".xls"
-
-
If .Show = True Then
-
For Each vrtSelectedItem In .SelectedItems
-
DoCmd.OutputTo acOutputQuery, "query or table name", acFormatXLS, vrtSelectedItem
-
Next vrtSelectedItem
-
Else
-
MsgBox "No file was selected"
-
End If
-
-
End With
-
-
End Sub
-
If anyone knows of any improvedments or enhancements to this routine feel free to offer advice.
Mary
