Using msoFileDialogSaveAs in MS Access

[MMcCarthy]

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

[ADezii]

Since AllowMultiSelect = True, you actually don't need the For...Next Loop. I know, a trivial point, just thought that I would mention it anyway.

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
       DoCmd.OutputTo acOutputQuery, "query or table name", acFormatXLS, .SelectedItems(1)
     Else
       MsgBox "No file was selected"
     End If
End With

[MMcCarthy]

;) Trivial points are all good

I adapted this from another procedure and just left the for loop in. Lazy I know :)

[TheSmileyCoder]

An issue I have had with the SaveAs dialog is that I cannot seem to force it to be a specific file type. For instance it would allow me to type C:\MyFileName.PDF when I want to force the user to save as excel for example.

The issue is that I cannot set the filter type for the SaveAs dialog. So I have had to check the fileextension after a path is selected. This is the code I ended up with, to whom it may concern:

Private Sub btn_Browse_Click()
   Dim oFD As FileDialog
   Dim strExtension As String
   Dim strValidExtensions As String
 
   'Define valid extensions
      strValidExtensions = ".xlsx;.xlsm"
 
   'Open filedialog object
      Set oFD = Application.FileDialog(msoFileDialogSaveAs)
 
   oFD.InitialFileName = "CustomExport" & Format(Date, "yyyy\-mm\-dd") & ".xlsx"
 
tryAgain:
   If oFD.Show() Then
      strExtension = GetFileExt(oFD.SelectedItems(1))
      If strExtension = "" Then
         'No extension typed, set manually to excel
         Me.tb_ExcelPath = oFD.SelectedItems(1) & ".xlsx"
      Else
         If InStr(1, strValidExtensions, strExtension) <= 0 Then
            MsgBox "Invalid file type selected"
            GoTo tryAgain
         End If
      End If
 
      Me.tb_ExcelPath = oFD.SelectedItems(1)
 
   End If
   Set oFD = Nothing
End Sub

GetFileExt is a custom function:

Public Function GetFileExt(strFilePath As String) As String
    Dim strArray() As String
    strArray = Split(strFilePath, ".")
    If UBound(strArray) <= 0 Then
      'Input is empty, or without extension
      GetFileExt = ""
    Else
      GetFileExt = "." & strArray(UBound(strArray))
   End If
End Function

[NeoPa]

You may find the Select a File or Folder using the FileDialog Object article helps there Smiley. Let me know here if not.