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.
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?
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 Subcompile error
dim fd As FileDialog
Any ideas?
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.
Ryan, I have the same error when running Debug > Compile. Which Reference library is needed for this function?
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: http://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/
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?
Hello Bob – xlsx is an xml format. In the selection for type, you should see 12 with XML. I recommend giving that a try.
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?
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 FunctionYou can call it as either: saveFileAs OR saveFileAs(“YourFileName.xlsx”)
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?
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
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.
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.
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: http://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/
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! 🙂