473,842 Members | 1,963 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select a File or Folder using the FileDialog Object

NeoPa
32,584 Recognized Expert Moderator MVP
Overview

It is often required to prompt the operator of an Access project to select a file or folder. Sure, it's possible to allow them to type it in freehand, or even paste it in from another application, but generally operators are more comfortable with the facility to select it from the existing file structure as they would in most Windows applications (with a file/folder selectioon window).

This is perfectly possible to do in Access (and any MS Office application of course, but we'll deal with Access here for simplicity). The details of what to do and how to do it can be found below. Attached to this post you can find an example database to play with which includes all this already implemented. There is also an associated video at VBA Tip: Select File or Folder using FileDialog in Access (cc) .


Objects

A single object is necessary. To implement this facility you use the FileDialog object from the MS Office Object library. This requires that the library is included as a reference of the project. If you don't find it already selected in the Tools \ References list (in the VBA IDE or Code Editor window) then select it using that same command. Versions differ, but the full name will probably look similar to "Microsoft Office XX.X Object Library". You may have to scroll down a long way to find it but most of the items in the list are arranged alphabetically.


File or Folder

Selecting files and selecting folders are very similar, but I'll cover the difference here just to be clear.

When selecting a file the operator can choose a folder either way (from the Look in: drop-down or the main section where files/folders within current folder are listed) and it will continue navigating into that folder. It will only return a value when a file is selected.

When selecting a folder however, it is still possible to navigate the folder structure using the Look in: drop-down, and even by double-clicking on a folder from the main section. To return the folder to the calling code, it is necessary to activate the OK button with a particular folder selected from the main section.


Example Database

To illustrate the usage of this I will knock up a simple database with a form containing two controls for selecting :
  1. A folder.
  2. A file.

As I don't want to do anything to anyone's files, the payload will simply be to display the full path of the folder or file in the associated label by the Command Button.

Structure and Objects of Example Database
There is very little to this in truth (See picture below for what it looks like).
  1. A form called frmFileDialog which has on it, apart from a title Label, an Exit button and its associated Label, two Command Buttons called cmdFolder and cmdFile, and an associated label for each (lblFolder and lblFile).
  2. The module associated with the form which contains the VBA code supporting the various controls.
  3. Another module, modMain, which simply contains the code for FSBrowse().




VBA Code

Let's start with a routine that provides a simple interface for using this object. I've called it FSBrowse and it's a function procedure.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'FSBrowse (File System Browse) allows the operator to browse for a file/folder.
  5. '  strStart specifies where the process should start the browser.
  6. '  lngType specifies the MsoFileDialogType to use.
  7. '  strPattern specifies which FileType(s) should be included.
  8. Public Function FSBrowse(Optional strStart As String = "", _
  9.                          Optional lngType As MsoFileDialogType = _
  10.                              msoFileDialogFolderPicker, _
  11.                          Optional strPattern As String = "All Files,*.*" _
  12.                          ) As String
  13.     Dim varEntry As Variant
  14.  
  15.     FSBrowse = ""
  16.     With Application.FileDialog(dialogType:=lngType)
  17.         'Set the title to match the type used from the list
  18.         .Title = "Browse for "
  19.         Select Case lngType
  20.         Case msoFileDialogOpen
  21.             .Title = .Title & "File to open"
  22.         Case msoFileDialogSaveAs
  23.             .Title = .Title & "File to SaveAs"
  24.         Case msoFileDialogFilePicker
  25.             .Title = .Title & "File"
  26.         Case msoFileDialogFolderPicker
  27.             .Title = .Title & "Folder"
  28.         End Select
  29.         If lngType <> msoFileDialogFolderPicker Then
  30.             'Reset then add filter patterns separated by tildes (~) where
  31.             '  multiple extensions are separated by semi-colons (;) and the 
  32.             '  description is separated from them by a comma (,).
  33.             '  Example strPattern :
  34.             '  "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
  35.             Call .Filters.Clear
  36.             For Each varEntry In Split(strPattern, "~")
  37.                 Call .Filters.Add(Description:=Split(varEntry, ",")(0), _
  38.                                   Extensions:=Split(varEntry, ",")(1))
  39.             Next varEntry
  40.         End If
  41.         'Set some default settings
  42.         .InitialFileName = strStart
  43.         .AllowMultiSelect = False
  44.         .InitialView = msoFileDialogViewDetails
  45.         'Only return a value from the FileDialog if not cancelled.
  46.         If .Show Then FSBrowse = .SelectedItems(1)
  47.     End With
  48. End Function
This routine allows a fairly simple interface and returns the selected item if any is selected, but an empty string otherwise. It also has the benefit of illustrating some of the properties of this object that are typically used.

Next is the code behind the form which calls the function above. Very little to explain as the complexity level is so basic, but I'll include a few pointers below the code anyway.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5.     Call DoCmd.Restore
  6.     With Me
  7.         .lblFolder.Caption = Replace(CurrentProject.Path & "\", "\\", "\")
  8.         .lblFile.Caption = CurrentProject.FullName
  9.     End With
  10. End Sub
  11.  
  12. Private Sub cmdFolder_Click()
  13.     Dim strPath As String
  14.  
  15.     With Me
  16.         strPath = FSBrowse(strStart:=.lblFolder.Caption, _
  17.                            lngType:=msoFileDialogFolderPicker)
  18.         If strPath > "" Then _
  19.             .lblFolder.Caption = Replace(strPath & "\", "\\", "\")
  20.     End With
  21. End Sub
  22.  
  23. Private Sub cmdFile_Click()
  24.     Dim strPath As String
  25.  
  26.     With Me
  27.         strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
  28.         strPath = FSBrowse(strStart:=strPath, _
  29.                            lngType:=msoFileDialogFilePicker, _
  30.                            strPattern:="MS Access Databases,*.ACCDB; *.MDB~" & _
  31.                                        "All Files,*.*")
  32.         If strPath > "" Then .lblFile.Caption = strPath
  33.     End With
  34. End Sub
  35.  
  36. Private Sub cmdExit_Click()
  37.     Call DoCmd.Close
  38. End Sub
  • Lines #4 - #10 Form_Open()
    This restores the form (in case it's either maximised or minimised).
    It also sets up the starting values of the labels that display both the folder (Line #7) and the file (Line #8). These values match, respectively, the folder and full name values for the current projuect. IE. The open database called
    Notice that it is only necessary to use Replace() for the folder as folder addresses can be returned either with or without a trailing slash (/) character. This code ensures one is added if, and only if, none is there already.
  • Lines #12 - #21 cmdFolder_Click ()
    Lines #13 - #15 and #20 - #21 simply set up the code structure and don't actually do anything specific.
    Lines #16 - 17 call the FSBrowse function shown above. The strStart parameter tells it where to start browsing from. In this case it is the same as originally set up in Form_Open() or the value last returned if it has been run before. The lngType parameter tells it to look for a folder.
    Lines #18 - #19 simply save the returned value in the Caption of the label if if the FileDialog wasn't cancelled. Notice it also adds a back-slash (\) at the end, but only if there is not one there already.
  • Lines #23 - #34 cmdFile_Click()
    Lines #23 - #26 and #33 - #34 simply set up the code structure and don't actually do anything specific.
    Line #27 sets up the starting address. It ensures that the file name part is dropped otherwise the visible files are filtered to match only that name. Essentially it sets the value to match the folder part only of the last returned value from the label's Caption.
    Lines #28 - 31 call the FSBrowse function shown above. The strStart parameter tells it where to start browsing from. In this case it is set by line #27. The lngType parameter tells it to look for a file. The strPattern parameter specifies which filters to make available for use.
  • Lines #36 - #38 cmdExit_Click()
    Line #37 simply closes the current object which is the form.

Conclusion

This is actually a very easy thing to accomplish and to incorporate into any of your databases. Once the basics are understood there's no reason why it shouldn't be used anywhere and everywhere such a facility is called for.
Attached Images
File Type: jpg FileDialog.jpg (26.0 KB, 55204 views)
Attached Files
File Type: zip FileDialog.Zip (105.6 KB, 8915 views)
Aug 26 '11 #1
11 80841
V4Friend
2 New Member
Hello NeoPa,

Thanks for your example!

There is one problem though: the .Filters.Add function throws error 438 (property or method not supported). For browsing a Folder I can bypass that, but not for selecting a file...

I have tried your mdb on both Access 2003 and Access 2010: both respond the same. Of course, I did set the right reference...
Mar 25 '13 #2
zmbd
5,501 Recognized Expert Moderator Expert
Neo,
V4Friend is correct:

When the dialog is set to 'msoFileDialogF olderPicker"
Expand|Select|Wrap|Line Numbers
  1. For Each varEntry In Split(strPattern, "~")
  2.     Call .Filters.Add(Description:=Split(varEntry, ",") _
  3.        (0), Extensions:=Split(varEntry, ",")(1))
  4. Next varEntry
Will give the "unsupporte d" error.

When the function is used with the remaining file selection types, the code runs as intended...

Two options, Trap for error 438 and recover from the error (more than likely what I would do) or if we alter 33 to 37 in the first code block to something like:
Expand|Select|Wrap|Line Numbers
  1. <...>
  2. '>[Z{Used a select case here as I dislike the IF then for multiple true cases.}]
  3.         Select Case lngType
  4.         Case msoFileDialogOpen, msoFileDialogSaveAs, msoFileDialogFilePicker
  5.             Call .Filters.Clear
  6.             For Each varEntry In Split(strPattern, "~")
  7.                 Call .Filters.Add(Description:=Split(varEntry, ",")(0), _
  8.                               Extensions:=Split(varEntry, ",")(1))
  9.             Next varEntry
  10.         End Select
  11. <...>
We can avoid the nasty error all togeither, which was what I was taught to do.
Mar 25 '13 #3
V4Friend
2 New Member
Hi zmbd,

Thanks. That's my opinion too: if you know an error is going to be thrown, try to prevent that.

It's working now. But I think it would have been better, if the FileDialog would just ignore a filter, instead of throwing an error. If one uses Intellisense, there is no telling it will fail in some cases...
Mar 26 '13 #4
NeoPa
32,584 Recognized Expert Moderator MVP
I need to look into this.

I've been away on a heavy project recently and have a lot to catch up with, but it sounds like you've done well to bring this to my attention. I appreciate that :-)
Mar 26 '13 #5
KSBaccess
1 New Member
Thank you sooooo much for the simple code! :-)
I like that the browse has a starting point. There's only 1 issue I'm having. Although the correct folder is highlighted in the browse window, only the last 13 characters of the folder name are appearing in the "Folder name" box. If the user clicks OK, an error is displayed indicating "Path does not exist."
Granted, these test folder names are very long (the final folder names used will most likely be shorter) and the user is only going to browse if they want to change the folder anyway....
I just wanted to understand how this works and remove the chance of the user getting an error message.

Example:
folder name: QRY4 EXCEL FILE GOES HERE
folder name text box displays: FILE GOES HERE

Thanks!

Peace.....
May 16 '13 #6
NeoPa
32,584 Recognized Expert Moderator MVP
This took a while to look at. Sorry.

It isn't appropriate to specify a filter when selecting a folder. I've updated the code in the OP to set filters in the routine only when :
Expand|Select|Wrap|Line Numbers
  1. lngType <> msoFileDialogFolderPicker
I believe this should resolve the issue. Thanks for the comments.
Feb 9 '14 #7
NeoPa
32,584 Recognized Expert Moderator MVP
KSBAccess. Sorry to take so long to respond.

We don't generally answer questions IN an article. They are done in separate threads. In this case though you may find that enlarging your label controls will show more of the information that you currently are getting cropped. You're simply seeing the last line of data. If this doesn't resolve your issue then please do not respond in here, but post a new thread with your question.
Feb 9 '14 #8
NeoPa
32,584 Recognized Expert Moderator MVP
I just updated the OP to include a link to the video I posted that accompanies this article (VBA Tip: Select File or Folder using FileDialog in Access (cc) ).
Oct 5 '14 #9
Jerry Norbury
2 New Member
Maybe it's me, but this generates this same error with the msoFileDialogSa veAs type too.
Mar 18 '15 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1953
by: Bryan Lynn | last post by:
Is there any way to store a GUID value as a property on a file/folder? When my app creates new files/folders, I'd like to store a GUID as a custom property on the file/folder. This way, when the user physically moves the file/folder using Windows Explorer (and not using my app), I can use this GUID to detect which files/folders were moved and my app can internally re-map some things. Any thoughts?
1
2184
by: strutsng | last post by:
I want a web page that allows the user to select a folder as input, not files. If I use <input type="file">, I couldn't select a folder as input, I can only select a single file as input. In my case, are there any solutions to my problem? Should I use VB component? But this only restrict me to create the web page using ASP, but not non-microsoft technology? please advise... thanks!!
5
21395
by: Phil Stanton | last post by:
Can anyone let me have the code for opening the open file dialog box and getting either a file or folder name as the output. I suspect it will look like Function GetPathOrFile(InputPath as String) As String ...... End function I seem to be able to get one or the other, but not both Files and Folders. I
2
5051
by: John Regan | last post by:
Hello All I am trying to find the owner of a file or folder on our network (Windows 2000 Server) using VB.Net and/or API. so I can search for Folders that don't follow our company's specified folder structure and naming conventions and then send a Net send message to those users telling them to rectify. The information I want to get is when you select the file/folder and then: Properties -> Security Tab -> Advanced Button -> Owner Tab ->...
10
14310
by: kai | last post by:
Hi, All I am trying to create a file folder for any login user, and create sub folders for the user on a web page. After the user login again, he can only sees his own folder on the Web page. I am planning to use VB2005 or C#. I look the help files and searched on the web, I cannot find the answer. Is this possble in ASP.NET 2.0? Thanks
3
3645
by: sunilrkp | last post by:
javascript code to save the contents of a textarea to a file using FIleDialog.
2
3429
by: Kuldeep | last post by:
Hi all, I have a requirement, where the user has to select a folder and all the files in that folder have to be imported to my databse. To acheive this i need a control which enables the user to select only a "FOLDER", and does not expect the user to select a file. When "html - fileInput" or "asp:FileUpload" is used, these controls do not accept a selected folder and expects a single file to be selected.
16
17833
by: technocraze | last post by:
Hi pals, I would like to know how to display the FileName of the selected file in the textbox and open it using FileDialog property. I have imported the necessary reference from the library - Microsoft Office Object Library 10.0. Tks Control: TextBox - Display FileName Button - Open Code:
2
4312
by: sajtovi007 | last post by:
Hi, I've found code in thread: Using FileDialog property to open File and display FileName in a textbox but: 1. I do not know how to add it in to ACCESS 2003 database. 2. I have fields in database with FirstName, LastName and IDnumber. How to Create folder named "LastName FirstName IDnumber" from ACCESS 2003 (on a click on a batton)? 3. And, additionaly, is it possible to add all files from this folder (folder created and opened in...
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10942
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10671
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10310
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9452
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7855
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5884
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3142
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.