423,846 Members | 1,959 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using msoFileDialogSaveAs in MS Access

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendtoExcel_Click()
  2. Dim fd As FileDialog
  3. Dim Title As String
  4. Dim vrtSelectedItem As Variant
  5.  
  6.     Set fd = Application.FileDialog(msoFileDialogSaveAs)
  7.  
  8.     With fd
  9.         .AllowMultiSelect = False
  10.         .Title = "Save File"
  11.         .InitialFileName = "Name Of Report " & Format(Now(), "ddmmyyyyhhnn") & ".xls"
  12.  
  13.         If .Show = True Then
  14.             For Each vrtSelectedItem In .SelectedItems
  15.                 DoCmd.OutputTo acOutputQuery, "query or table name", acFormatXLS, vrtSelectedItem
  16.             Next vrtSelectedItem
  17.         Else
  18.             MsgBox "No file was selected"
  19.         End If
  20.  
  21.     End With
  22.  
  23. End Sub
  24.  
If anyone knows of any improvedments or enhancements to this routine feel free to offer advice.

Mary
Nov 13 '09 #1
Share this Article
Share on Google+
4 Comments


ADezii
Expert 5K+
P: 8,591
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.
Expand|Select|Wrap|Line Numbers
  1. Dim fd As FileDialog
  2. Dim Title As String
  3. Dim vrtSelectedItem As Variant
  4.  
  5. Set fd = Application.FileDialog(msoFileDialogSaveAs)
  6.  
  7. With fd
  8.   .AllowMultiSelect = False
  9.   .Title = "Save File"
  10.   .InitialFileName = "Name Of Report " & Format(Now(), "ddmmyyyyhhnn") & ".xls"
  11.  
  12.      If .Show = True Then
  13.        DoCmd.OutputTo acOutputQuery, "query or table name", acFormatXLS, .SelectedItems(1)
  14.      Else
  15.        MsgBox "No file was selected"
  16.      End If
  17. End With
Dec 16 '09 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
;) Trivial points are all good

I adapted this from another procedure and just left the for loop in. Lazy I know :)
Dec 16 '09 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Browse_Click()
  2.    Dim oFD As FileDialog
  3.    Dim strExtension As String
  4.    Dim strValidExtensions As String
  5.  
  6.    'Define valid extensions
  7.       strValidExtensions = ".xlsx;.xlsm"
  8.  
  9.    'Open filedialog object
  10.       Set oFD = Application.FileDialog(msoFileDialogSaveAs)
  11.  
  12.    oFD.InitialFileName = "CustomExport" & Format(Date, "yyyy\-mm\-dd") & ".xlsx"
  13.  
  14. tryAgain:
  15.    If oFD.Show() Then
  16.       strExtension = GetFileExt(oFD.SelectedItems(1))
  17.       If strExtension = "" Then
  18.          'No extension typed, set manually to excel
  19.          Me.tb_ExcelPath = oFD.SelectedItems(1) & ".xlsx"
  20.       Else
  21.          If InStr(1, strValidExtensions, strExtension) <= 0 Then
  22.             MsgBox "Invalid file type selected"
  23.             GoTo tryAgain
  24.          End If
  25.       End If
  26.  
  27.       Me.tb_ExcelPath = oFD.SelectedItems(1)
  28.  
  29.    End If
  30.    Set oFD = Nothing
  31. End Sub
GetFileExt is a custom function:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetFileExt(strFilePath As String) As String
  2.     Dim strArray() As String
  3.     strArray = Split(strFilePath, ".")
  4.     If UBound(strArray) <= 0 Then
  5.       'Input is empty, or without extension
  6.       GetFileExt = ""
  7.     Else
  8.       GetFileExt = "." & strArray(UBound(strArray))
  9.    End If
  10. End Function
Jan 29 '13 #4

NeoPa
Expert Mod 15k+
P: 31,121
You may find the Select a File or Folder using the FileDialog Object article helps there Smiley. Let me know here if not.
Jan 29 '13 #5