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!
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.
Double check your line “Set fd = Application.FileDialog(msoFileDialogFilePicker)”
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.
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.
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.
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!
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
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!
Hi,
How can I select multiple sheets in a workbook to import multiple tables in in access useing above procedure.
Thanks,
Hari
Thanks for these two functions. I did a bit of searching before finding this page. Now, my search is over.
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!
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.
Bless you, brother!..can’t wait to try this. I’ll let you know
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!
Rats! I think I see my problem. I’m using Access 2007 so I can’t set those objects from the Object Library
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”
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…
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.
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.
Sweet!
Worked like a charm!
Thank you!
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
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 FunctionLooks good, glad to help
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!
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 loadFilesSub 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 SubRyan,
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 🙂
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.Printline. 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.from MS Access (if that’s what you are asking). Yes, I did removed the ” ‘ ” but still not debug window appeared 🙁
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.
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~!
Is there a way to specify the coordinates of the filedialog to place it in a certain position?
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?