• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Select or Open a file in VBA with file dialog | Access VBA

August 22, 2013 by Ryan McCormick 32 Comments

In a recent project, I was tasked with designing a program in Access where an end user could import a spreadsheet, run a sub routine in VBA and export the results.

As part of the user experience, I wanted the end user to be able to select the input spreadsheet with the file dialog tool.

UPDATE 05/22/2015: The function below contains an error handler and works well if your input has the potential to throw an error. For simplicity-sake, I have included a more compact version (short-circuited) under this one:

'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Function selectFile()
Dim fd As FileDialog, fileName As String

On Error GoTo ErrorHandler

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fileName = fd.SelectedItems(1)
    End If
Else
    'Exit code if no file is selected
    End
End If

'Return Selected FileName
selectFile = fileName

Set fd = Nothing

Exit Function

ErrorHandler:
Set fd = Nothing    
MsgBox "Error " & Err & ": " & Error(Err)

End Function

Compact version of File Dialog Code

Please note, you still need to include a reference to the Microsoft Office 14.0 Object Library. I have tried every trick I could find to implement late binding (bind in code without reference). I have found that you simply can’t implement without reference (early binding). If anyone out there can prove me wrong on this, please do comment.

'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Function selectFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        If .Show Then
            selectFile = .SelectedItems(1)
        Else
            End
        End If
    End With
    Set fd = Nothing
End Function

Select Multiple Files With File Dialog

The original code from when I first built this post allowed users to select multiple files. Since VBA doesn’t do objects/arrays like other programming languages, it is difficult to make the function return an array or object. So, I built my example in a Public Sub and marked up the area that returns selected filenames with a loop. My assumption is that if you need to select multiple files in VBA, you will probably have an idea for how to implement. If you run into a wall, add a comment and we can work on a solution.

'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Public Sub multFileSelect()
Dim fd As FileDialog, fileName As String
Dim vrtSelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = True
    
If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        For Each vrtSelectedItem In fd.SelectedItems
            '''''''''''''''''''''
            'Selected Files in loop
            ''''''''''''''''''''''
            MsgBox vrtSelectedItem
        Next vrtSelectedItem
    End If
Else
    'Exit code if no file is selected
    End
End If
    
Set fd = Nothing
 
End Sub

As always, please comment!

Related

Filed Under: Microsoft Access Tagged With: file dialog, microsoft access 2010, vba

Reader Interactions

Comments

  1. Patty says

    March 25, 2014 at 10:38 am

    when I try to use this function i get the error user defined type not defined for the line:
    Dim fd As FileDialog.

    I do have the office 14 objects in my list of references.

    Can you help please?
    Thanks.

    Reply
    • Ryan McCormick says

      March 25, 2014 at 12:34 pm

      Double check your line “Set fd = Application.FileDialog(msoFileDialogFilePicker)”

      Reply
      • GCD says

        August 19, 2014 at 12:04 pm

        I am receiving a compile error (user-defined type not defined) related to the Dim fd As FileDialog line. Also- I’m interested in learning how to apply this code functionally. I’m looking to have this code by driven by a button on a form.

        I need to be able to select one worksheet from the selected excel file to be imported into a specified table in my database.

        Finally, I need to run an append query off of the table into which the excel data is imported and later run a separate query that deletes all of the data in the same table. Generally, I can do all of this using the macro-builder. However I don’t know how to achieve this when using a function that I coded with VBA, Thanks in advance.

        Reply
        • Ryan McCormick says

          August 19, 2014 at 12:42 pm

          In regards to your first question – did you add a reference to the Microsoft Office Object library? Without the reference, the functionality does not exist as there is no reference to it. You can find references in the VBA script area: Tools>References. Check the box next to the correct Office Object Library (14 if Access 2010, 15 if Access 2013).

          Your next question about importing – it looks like you posted a comment on that and it worked well correct?

          Your third question about running adhoc update, delete and append queries. If you don’t have the need to return a value you can use DoCmd.RunSQL “UPDATE table…”. If the function is tested and want to run it without warnings popping up, you can wrap it with DoCmd.SetWarnings False -run queries here- DoCmd.SetWarnings True.

          Reply
  2. Paul says

    March 28, 2014 at 9:14 am

    Ryan,
    Thanks for this, I was having a hard time finding a way to make this work. I ended up using your original code and that is working well. Is it possible to filter the results in the dialog so that I only see .xls (or .xlsx) files? I am also getting an error if I press cancel in the file open dialog box.

    Reply
    • Paul says

      March 28, 2014 at 4:02 pm

      Ryan, thanks for having this posted, I was able to work through the code and fix my other issues. Not bad for a biologist pretending to be an Access database designer. .:)

      Thanks Again!

      Reply
      • Ryan McCormick says

        March 29, 2014 at 9:25 pm

        Very cool! I am happy that you were able to make it work! I just noticed your other comment where you were getting an error when pressing cancel. When clicking cancel, the result returned is null and normally creates an error when moving to the next step of loading the file name (in this case your file name/path is value 0). An “if” statement might work for you to handle the 0 in this case:

        If Format(FileName) = vbNullString Then
        'Do Nothing because returned value is zero
        Else
        'Execute code because returned filename value not zero
        'Returned value should be the selected file name/path
        End If

        Reply
  3. Jordan says

    April 22, 2014 at 12:59 pm

    Spot on with this write-up, I absolutely believe that this amazing site needs a lot more
    attention. I’ll probably be returning to read more, thanks for the info!

    Reply
  4. Hari says

    August 5, 2014 at 7:55 am

    Hi,
    How can I select multiple sheets in a workbook to import multiple tables in in access useing above procedure.

    Thanks,
    Hari

    Reply
  5. krazykaigh says

    April 7, 2015 at 7:27 am

    Thanks for these two functions. I did a bit of searching before finding this page. Now, my search is over.

    Reply
  6. richard says

    April 7, 2015 at 8:11 am

    I’m having a similar problem trying to create a button for a user menu which will allow the user to browse to the file for IMPORT. Currently i’m using the Macros within ACCESS to just have the file already in the proper place and named the proper name but that’s a bit cumbersome, but for now “its state of the art”. haha! but I need to be able to tackle this somehow. Bottom line is I have a “Particular Table” that needs this “Import” on a regular basis, but if I could just allow the user to see a Browse window then that would make it easier. My IMPORT goes right into a table called 2410 MASTER which I have to reference in ACCESS as [2410 MASTER] or course. Reason I need users to IMPORT this is because the users also routinely clear out a previous data set so that a fresh set of records can be IMPORTED for processing. ACCESS is just wonderful for all we do, but i’d sure like to have a Browse window for IMPORTING any selected Excel File regardless of its name into my [2410 MASTER] table. The reason is the user always preps the Excel file before hand and Lord knows where on earth it is or what its named by the time its ready for IMPORTING. I appreciate your genius! hahaha! I really do!

    Reply
    • Ryan McCormick says

      April 7, 2015 at 9:54 pm

      Hello Richard-

      I wrote a post that may help you with the file select>import method:
      Import Excel File to Access 2010, 2013 With File Dialog in VBA

      Also, I just added a couple of sample files to the article for you to download. You can find the link at the bottom of the article.

      The most important part of doing regular imports is having clean data and clean field names in your excel file (field names that match the target table in access). I have done a lot of projects like this, please let me know if you have any questions.

      Reply
      • Richard says

        April 8, 2015 at 12:06 pm

        Bless you, brother!..can’t wait to try this. I’ll let you know

        Reply
      • richard says

        April 9, 2015 at 8:18 am

        Thanks Ryan!
        i’m new at any level of VBA and I do my darndest to use SQL or any of my Excel knowledge or my database experience from years and years, but i’m at a zero level with vba.. lol… I’ve got a routine that already deletes my data in the table already (function on a menu, lol, for users) but I need this IMPORT button to execute what you have here. i’m guessing that I just create this function under BUILD or do I just make this as a MACRO and then the last question I just need to find the line in your code where it knows where to IMPORT the file into what table. I think I see where that is. i’m going to try to tackle this this morning. wish you were at the coffee pot and could follow me back and look over my shoulder!.. lol i’ll keep you posted.. here goes!

        Reply
      • richard says

        April 9, 2015 at 8:57 am

        Rats! I think I see my problem. I’m using Access 2007 so I can’t set those objects from the Object Library

        Reply
      • richard says

        April 14, 2015 at 12:01 pm

        i’m still having trouble… I finally got Access 2013 and I tried to add the two pieces of code (vba) but i’m getting errors. I hit control g and it brings up the vba things and I check on the object library and its 15.0 already checked… then while in vba window I added another module called module1 and added the FileDialogue code… not sure if that’s what i’m supposed to do or where.. also I created a button just like you had.. the command button part cmdLoad and put the latter code there under Build.. i’m just lost, but i’m sure i’m close.. when I click the button I get Compile Error: User-Defined Type not defined then the VBA window top 2 lines of the code “function line.. and Dim line”

        Reply
  7. richard says

    April 15, 2015 at 12:09 pm

    Ryan, I still get the Compile error: User-defined type not defined.. geeze.. I’ve downloaded your zip db and it works great, but I can’t replicate it in my db. I tell you what though.. I transferred your form, table to my db and I get the same error.. however.. if I just launch your db it works fine!.. lol.. the error I get then highlights the Function selectFile() Dim fs As FileDialog, filename As String…

    geeze.. I know i’m close, but…

    Reply
    • Ryan McCormick says

      April 16, 2015 at 7:10 pm

      Did you add the Microsoft Object Library as a reference? Open your VBA environment, click on the tools menu and select references. Check the box next to the Microsoft Office XX.0 Object Library, save and see if that works.

      Reply
    • Ryan McCormick says

      April 16, 2015 at 7:14 pm

      Oops, I just noticed your comment from the 14th. On second thought, you might want to check any of the data types you defined in the process. Check variables (example Dim myName as String) where you could be mixing a variant or something.

      Reply
  8. Richard says

    June 4, 2015 at 11:08 am

    Sweet!

    Worked like a charm!

    Thank you!

    Reply
  9. Guamer says

    November 11, 2015 at 7:49 pm

    In response to the topic of late-binding. This variation similar to your example worked for me w/o references.

    ———————————–
    Dim f As Object
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show

    MsgBox “number of files = ” & f.SelectedItems.Count
    MsgBox “First file choosen = ” & f.SelectedItems(1)
    ——————————————————-

    Source, Accepted Answer from:
    http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba

    Reply
    • Ryan McCormick says

      November 20, 2015 at 9:16 pm

      I see what you did there :). By using the enumerated version (3) of msoFileDialogFilePicker, the the late-binding method works! I have been racking my brain on this one for quite some time, I really appreciate you posting this! I am going to create a new post for this one, you mentioned stackoverflow, is this your solution? I would love to give you credit.

      Here is the example I am going to use on the site:

      'Late binding version of selectFile
      'No MS Office Object references needed
      '''''''''''''''''''''''''''''''''''''''
      Function selectFile()
          Dim fd As Object
          Set fd = Application.FileDialog(3)
          
          With fd
              If .Show Then
                  selectFile = .SelectedItems(1)
              Else
                  End
              End If
          End With
          
          Set fd = Nothing
      End Function
      
      Reply
      • Guamer says

        November 21, 2015 at 8:34 am

        Looks good, glad to help

        Reply
  10. Dat Vu says

    December 2, 2015 at 10:30 am

    Hi Ryan,

    first, thank you for awesome work, it helped me a lot to set up my db, considering I am a complete novice. I do run into a bit of a pickle. I am using the multifile selection but cannot seem to make my loop work.I want to load the vrtSelectedItem into a FilePath array that will be used to import a file to db depending on how many files are selected. So far, I am testing to limit to 2 files but getting kind of lost. Here’s my code for the function.

    Private Sub multFileSelect(FilePath)
    Dim fd As FileDialog, fileName As String
    Dim vrtSelectedItem As Variant

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = True

    If fd.Show = True Then
    If fd.SelectedItems(1) vbNullString Then
    For i = 0 To 1
    For Each vrtSelectedItem In fd.SelectedItems
    ””””””””””’
    ‘Selected Files in loop
    ”””””””””””

    ‘MsgBox vrtSelectedItem
    FilePath(i) = vrtSelectedItem
    Next vrtSelectedItem

    MsgBox FilePath(i)
    Next
    End If
    Else
    ‘Exit code if no file is selected
    End
    End If

    Set fd = Nothing

    End Sub

    The filepath variable will be used then in:
    DoCmd.TransferText acImportDelim, “”, “SEPT_Turnover Analysis – Combined”, FilePath(0), True, “”
    DoCmd.TransferText acImportDelim, “”, “SEPT_Turnover Analysis – Combined”, FilePath(1), True, “”

    Hope it makes sense? Thanks for your help!

    Reply
    • Ryan McCormick says

      December 2, 2015 at 12:15 pm

      Hello Dat Vu,

      Without sample data, I can’t fully test this. My debug prints the filenames, so the only issue would be your DB table or the format of your input. Add this to a module and call it from your button or other event: Call loadFiles

      Sub loadFiles()
          Dim fd As FileDialog, vrtSelectedItem As Variant
          Set fd = Application.FileDialog(msoFileDialogFilePicker)
          
          On Error GoTo ErrorHandler
          
          With fd
              .AllowMultiSelect = True
              If .Show Then
                  DoCmd.SetWarnings False
                  For Each vrtSelectedItem In .SelectedItems
                  ' Import each file selected
                  DoCmd.TransferText acExportDelim, "", "SEPT_Turnover Analysis - Combined", vrtSelectedItem, True, ""
                 
                  ''''''''''''''''''''''''''''''''''''''''''
                  ' DEBUG:
                  ' Uncomment to show selected filenames
                  'Debug.Print "DEBUG: " & vrtSelectedItem
                  Next vrtSelectedItem
                  DoCmd.SetWarnings True
              Else
                  End
              End If
          End With
      
          Set fd = Nothing
          
          Exit Sub
          
      ErrorHandler:
          DoCmd.SetWarnings True
          MsgBox "Error " & Err & ": " & Error(Err)
          Set fd = Nothing
      End Sub
      
      Reply
      • Dat Vu says

        December 2, 2015 at 3:59 pm

        Ryan,

        thanks so much for the quick reply. I tried running the code you uploaded without incorporating it into anything just to see what it does but the debug.print window does not appear and the files don’t get imported. I am not sure where it went wrong?

        I apologize if I am missing something. It’s my first time working with this so please bear with me 🙂

        Reply
        • Ryan McCormick says

          December 2, 2015 at 7:13 pm

          Hello Dat Vu,

          Where are you calling your code from? To show the filenames in the immediate window, remove the “‘” comment mark from in front of the Debug.Print line. You can also test this by adding to a module, setting your text cursor on top of the loadFiles() part and clicking the run/play button at the top.

          Reply
          • Dat Vu says

            December 2, 2015 at 11:14 pm

            from MS Access (if that’s what you are asking). Yes, I did removed the ” ‘ ” but still not debug window appeared 🙁

          • Ryan McCormick says

            December 4, 2015 at 8:47 am

            Hello Dat Vu,

            It sounds like you are at the ground level of starting out. If you have a sample of the exact file – I can build a quick db for you to use and work off of.

            Looks like you are importing delimited text files so the formatting of your input data is going to have to be exact to get this to work consistently.

  11. Bill Johnson says

    August 18, 2016 at 1:10 pm

    Thank you for this! I was using a much older cold (that worked) but I needed to retain the file name to open a workbook, then use the same file to open several different sheets. This allowed me to specify a global variable (fileName) and reuse rather than having the user go through the process of selecting the workbook multiple times. Thanks again~!

    Reply
  12. Federico Bañares says

    November 30, 2017 at 3:57 am

    Is there a way to specify the coordinates of the filedialog to place it in a certain position?

    Reply
  13. Matt says

    September 11, 2019 at 11:11 am

    Good afternoon,
    So this adds the path, and it looks like a hyperlink, but when I click it, it wont open the file. I’m sure its something simple that I am missing. Can you help?

    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