468,117 Members | 1,396 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Using msoFileDialogSaveAs in MS Access

MMcCarthy
14,534 Expert Mod 8TB
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
4 32038
ADezii
8,799 Expert 8TB
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
14,534 Expert Mod 8TB
;) 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
2,321 Expert Mod 2GB
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
32,026 Expert Mod 16PB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Patrick Blackman | last post: by
5 posts views Thread by jhcorey | last post: by
13 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.