File Dialog Save-As in VBA Export Table by Name | Microsoft Access

April 23, 2014 · Ryan McCormick · 16 comments

Sometimes it is necessary to export a table to create a backup, share data with a team member, or whatever… Here is how you export a table using the File Dialog object in Access with VBA. This example will work in Access 2007 – 2013.

UPDATE 02/16/2015: Added reusable code for file save-as dialog box.

Create a new module or add the following code to an existing module:


Public Function saveFileAs() As String
    Dim fd As FileDialog, fileName As String
    
    On Error GoTo ErrorHandler

    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    
    If fd.Show = True Then
        If fd.SelectedItems(1) <> vbNullString Then
            fileName = fd.SelectedItems(1)
        End If
    Else
        'Stop Code Execution for Null File String
        End
    End If
    
    saveFileAs = fileName
    
    'Cleanup
    Set fd = Nothing
    
    Exit Function
        
ErrorHandler:
    Set fd = Nothing
    MsgBox "Error " & Err & ": " & Error(Err)

End Function

Public Sub exportTable(tName As String)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tName, saveFileAs, True
End Sub

To use, place the following in the “_Click()” Action of a button:


	Call exportTable("mytable")

Replace “mytable” with the name of the table you would like to export.

Pretty straightforward. If you get stuck, please comment. Likewise if you have questions or comments.

Comments

Victoria August 5, 2019

Hi, I want to export multiple tables into one excel sheet with one file dialog box. What would be the best way to go about that? Thanks! 🙂
Ryan McCormick March 19, 2017

You need to ensure you are referencing the MS Office object to get access to the FileDialog methods and functionality. Alternatively, you could implement late binding and remove the need to ref the library: https://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/
Ryan McCormick March 19, 2017

Microsoft Office 14.0 for Access 2010, 15.0 for Access 2013. I havent tested this with the latest Office version, but the new library should probably be 16.0. Alternatively, you can implement late binding if you need your solution to be version portable. For more details about how to set up late binding, check out my post here: https://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/
Dan Appleyard February 14, 2017

Ryan, I have the same error when running Debug > Compile. Which Reference library is needed for this function?
moh December 30, 2016

how did you figure this out….

I mean i tried
in the immediate window all of these things

application.FileDialog (msoFileDialogSaveAs)

and it only worked when i copied your whole code and ran it as a script.

thanks for your help.
Ryan McCormick December 8, 2015

Sounds like something weird is happening to your tName variable before calling the transfer function. Comment it out and right before it add debug.print tName to see whats happening. tName needs to call the name of the query or table you are exporting.
Chris December 7, 2015

I am desperately trying to make this worth with the docmd.TransferText method. I but I keep getting error 3011. MSaAccess database engine could not find the object ( “name”#txt)

I replaced the transferspreadsheet line with below:

DoCmd.TransferText acExportDelim, “02_EDD_Export_1130”, tName, saveFileAs, False, “”, 37

I would appreciate any suggestions. Thanks
Pieter June 17, 2015

Hi Ryan,

Thank you.
I’m calling saveFileAs from your exportTable function, which in turn is called from the On click event in my form (Call exportTable(“mytable”).

I’m trying to figure out how to pass the file name form the form to safeFileAs.
another option is to make te filename a constant in the function, but that too doesn’t seem to work.

Any ideas?

Ryan McCormick June 16, 2015

Hello Pieter – I made a modification to the code to accept an optional filename:


Public Function saveFileAs(Optional iFilename As String = "") As String
    Dim fd As FileDialog
 
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
 
    'Set initial filename
    fd.InitialFileName = iFilename
 
    If fd.Show = True Then
        If fd.SelectedItems(1) <> vbNullString Then
            saveFileAs = fd.SelectedItems(1)
        End If
    Else
        'Stop Code Execution for Null File String
        End
    End If
  
    'Cleanup
    Set fd = Nothing
 
End Function

You can call it as either: saveFileAs OR saveFileAs(“YourFileName.xlsx”)

Pieter June 11, 2015

Hi Ryan,

Thank you for sharing this code. I’ve changed acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12Xml as you suggested.
When using it to export and save a query, in the Save As dialog, the file name field is empty.
Is there a way to take the query name and use is as file name?
Ryan McCormick April 29, 2015

Hello Bob – xlsx is an xml format. In the selection for type, you should see 12 with XML. I recommend giving that a try.
Bob Snider April 29, 2015

Ryan, The code works great! However, when I specify 12 (or earlier as the spreadsheet type for Excel 2010 and the file extension “.XLSX”, Excel will not open the file because it’s in the wrong format. It’s almost as if the exportTable sub isn’t really working. Any ideas?

Ryan McCormick April 8, 2015

Yes, you save the path into a string variable and start your loop if your string is not Null or vbNullString.


Sub exampleSub()
  Dim fileNameStr as String: fileNameStr = selectFile()
  
  'Checks to make sure fileNameStr contains something otherwise
  'you will get errors
  If ISNULL(fileNameStr) = False Then
    'start your call to fileNameStr as many times as you need
  End If

End Sub

Ryan McCormick April 8, 2015

A compile error could be one of two things that come to mind. The reference has not been selected from the tools menu. Or perhaps the method (object, function, sub) does not accept the data type string. Remove “as string” after the function. It is difficult to tell without looking at the code, but these two are my best guesses.
Dean March 18, 2015

compile error

dim fd As FileDialog

Any ideas?
Tom February 26, 2015

Great code,
Question: If I am exporting multiple spreadsheets into one file, is there a way for me to call the selected path without selecting it over and over?

Comments

Leave a Reply