• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

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

April 23, 2014 by 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.

Related

Filed Under: Microsoft Access, VBA Tagged With: access 2013, ms access 2010, Save As File Dialog, vba

Reader Interactions

Comments

  1. Tom says

    February 26, 2015 at 4:50 pm

    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?

    Reply
    • Ryan McCormick says

      April 7, 2015 at 9:31 pm

      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
      
      
      Reply
  2. Dean says

    March 18, 2015 at 4:20 am

    compile error

    dim fd As FileDialog

    Any ideas?

    Reply
    • Ryan McCormick says

      April 7, 2015 at 9:27 pm

      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.

      Reply
      • Dan Appleyard says

        February 14, 2017 at 12:44 pm

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

        Reply
        • Ryan McCormick says

          March 19, 2017 at 3:58 pm

          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/

          Reply
  3. Bob Snider says

    April 28, 2015 at 7:02 pm

    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?

    Reply
    • Ryan McCormick says

      April 28, 2015 at 8:11 pm

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

      Reply
  4. Pieter says

    June 11, 2015 at 4:24 am

    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?

    Reply
    • Ryan McCormick says

      June 16, 2015 at 8:26 am

      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”)

      Reply
      • Pieter says

        June 17, 2015 at 2:36 am

        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?

        Reply
  5. Chris says

    December 7, 2015 at 2:02 pm

    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

    Reply
    • Ryan McCormick says

      December 7, 2015 at 6:11 pm

      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.

      Reply
  6. moh says

    December 30, 2016 at 10:15 am

    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.

    Reply
    • Ryan McCormick says

      March 19, 2017 at 4:05 pm

      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/

      Reply
  7. Victoria says

    August 5, 2019 at 9:29 am

    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! 🙂

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in