Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

External Files List in Hyperlinks


Is it possible to display a directory listing of External Files on a Form and open them in their parent-applications? These kinds of several queries have received through E-mails pointing towards this objective, from Readers of the earlier Article: File Brower in MS-Access (Common Dialog Control).

We have seen that we can open and work with external data sources like dBase Tables, Excel databases, and AS400(iSeries) Tables directly without linking them permanently with MS-Access and familiarized these procedures through the following Articles:

  • Opening External Data Sources
  • Opening dBase Files Directly
  • Display Excel Value Directly on Form
  • Opening Excel Database Directly
  • Database Connection String Properties
  • Access Live Data in Excel
  • Access Live Data in Excel-2
  • Source ConnectStr Property and ODBC
  • But, all of them fall into only one category, data files.

    Designing a Form

    To answer the above query we will create a Form with a Datasheet Sub-Form and with few simple controls to take a listing of a particular category of Files of your choice (Text Files, Word Files, Excel Files, or All Files) from a Folder (including sub-folders, if needed) and display them in a list. If you would like to open and work with them in their parent Applications, you are welcome to do so by clicking and opening it in their parent Application. An Image of a sample run is given below:

    All you have to do is to type the File Path like C:\Documents and Settings\UserName\My Documents\*.xls into a Text Box and click a Command Button and there it is; all excel files from the selected folder is presented on the Form as Hyperlinks on the Datasheet.

    I know you are not impressed because you could memorize and keep entering the above Path or some other Path without difficulty. Then what about other numerous locations and sub-folders on several Disk Drives? Opening Windows Explorer is out of the question; to find the location and enter it into the Text Box Control manually. Well, if there is no other way then that also can be done.

    Finding Files on Folders

    No, you will click on the Path Lookup Command Button to invoke something like the Common Dialog Control and browse to the location of the files you are interested in, select one of them and click the OK button. The File's location address with the File you have selected will be placed in the Text Control.

    That saves a lot of trouble in finding the location of the files. It takes only a few clicks of the mouse and we are doing it plenty of times a day.

    You may bring in the selected file into the list in Datasheet view by clicking the Get Files Command Button or modify the file name part with Wild Card characters (like *.xls, or Acc*.jpg) to bring in a group of files into the list.

    The DataSheet Form Design

    The Form has a simple design as you can see from the image given above. The following are the main elements of the design.

    1. A Table: DirectoryList with a single Field FileLinks with data type Hyperlink.
    2. A Datasheet Form created on the above Table with the name FilesListing_Sub.
    3. The Main Form FilesListing with the Datasheet Form inserted as Sub-Form that occupies the major part of the design.
    4. A Command Button (Name: cmdPathLookup) with the Caption Path Lookup runs the Sub-Routine and works like Common Dialog Control to browse and select a file from the required location and insert the Path Name in the Text Box below.
    5. A Text Box with the name PathName to hold the location address (or Path Name of the Files) and to modify as per your requirement before you Click the Get Files Command Button. If you know the Path Name correctly then you may type it in the Text Box directly, whichever is easier for you.
    6. The Command Button with the name cmdGo and with the Caption Get Files when clicked brings in all the files matching the Path Name specified and displays them in the Data Sheet View above.
    7. A Combo-Box with the name cboYN with Yes, No Values. If you set the value to Yes before clicking the Get Files Command Button; all files from the Sub-Folders of the selected folder, if any, also included in the output.
    8. The Command Button with the name cmdClose and with the Caption Close when clicked closes the FilesListing Main Form.

      The Property Sheet Values

    9. Display the Property Sheet(View- ->Properties) of the Main Form FilesListing and change the Property Values as given below to modify its behavior when opened:
      • Caption = External Files List
      • Default View = Single Form
      • Allow Edits = Yes
      • Allow Deletions = Yes
      • Allow Additions = No
      • Scroll Bars = Neither
      • Record Selectors = No
      • Navigation Buttons = No
      • Dividing Lines = No
      • Auto Re-Size = Yes
      • Auto Center = Yes
      • Pop up = Yes
      • Modal = No
      • Border Style = Dialog
      • Control Box = Yes
      • Min Max Buttons = None
      • Close Button = Yes
      • Allow Design Changes = Design View Only

    The Main Form Design

    The image of the Main Form in design view is given below:

    The heading Labels External Files List is created with the 3D Text Creation Program and modified to look like the above design. You can download this 3D Text Creation Wizard from any of the following links:

Managing the Files List

The Files' List is created as Hyperlinks in the target table: DirectoryList, every time, replacing the existing list of files, and appears in Datasheet View. You may click on the HyperLink to open that file in its parent Application to work with it.

You can delete unwanted files from the list. Select a record by clicking on the left border of the list and press Delete Key or press Ctrl+- (press and hold Ctrl Key and press - Key). You cannot add a record manually or edit a record on the Datasheet (you may do so directly on the Table if you like to mess around with it) so that the HyperLink Value Segments are not altered.

If you would like to know more about the Hyperlink value Segments (four of them) and what they do; go to the link Open Forms with Hyperlinks in ListBox.

You may download the sample database from the bottom of this post and try it out before you design one of your own to understand it better.

You can easily implement this in your various Projects by simply importing the Forms and the Table into them.

It is an Access2000 Version file and Access97 users cannot use it. But, you can design the above Forms and Controls with their specific names as explained above by following Step-1 to 9.

The Main Form Class Module Code

The Sub-Routines and Function which runs behind the Main Form FilesListing are given below. You may create the above design with the correct names explained under Steps-1 to 9, copy and paste the complete code in the Main Form Class Module and save the Form.

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdGo_Click()
Dim strtxt As String
On Error GoTo cmdGo_Click_Err
strtxt = Nz(Me![PathName], "")
New_Search strtxt, Me!cboYN

Exit Sub

MsgBox Err.Description, , "cmdGo_Click"
Resume cmdGo_Click_Exit
End Sub

Private Sub cmdPathLookup_Click()
'Source Code : Northwind.mdb sample database
'Displays the Office File Open dialog to choose a file name    
'If the user selects a file    
'display it in the Pathname control.    
Dim result As Integer    
On Error GoTo cmdPathLookup_Click_Err
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select a File from Target Folder"
        .Filters.Add "All Files", "*.*"
        .FilterIndex = 1
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.path
        result = .Show
        If (result  0) Then
            Me!PathName = Trim(.SelectedItems.Item(1))
        End If
    End With

Exit Sub

MsgBox Err.Description, , "cmdPathLookup_Click"
Resume cmdPathLookup_Click_Exit
End Sub

Private Sub Form_Load()
  Me!PathName = CurrentProject.path & "\*.*"
End Sub

Private Function New_Search(ByVal strFilePathName As String, boolsubfolders As Boolean)
'Author : a.p.r. pillai
'Date   : Feb. 2009
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
Dim i, strFolder As String, strFiles As String, locn As Long
Dim db As Database, rst As Recordset, xtn As Integer

'On Error GoTo New_Search_Err

If Len(Trim(strFilePathName)) = 0 Then
   MsgBox "File Path Name required."
   Exit Function
End If
xtn = InStr(1, strFilePathName, ".")
locn = InStrRev(strFilePathName, "\")

If xtn > 0 And locn > 0 Then
   strFiles = Trim(Mid(strFilePathName, locn + 1))
   strFolder = Trim(Left(strFilePathName, locn - 1))
ElseIf xtn > 0 And locn = 0 Then
   strFiles = Trim(strFilePathName)
   If Len(Left(strFiles, xtn - 1)) = 0 Then
      MsgBox "Invalid File specification."
      Exit Function
   End If
   strFolder = CurrentProject.path & "\*.*"
ElseIf xtn = 0 And locn > 0 Then
   strFiles = "*.*"
   strFolder = Trim(strFilePathName)
End If

With Application.FileSearch
'the .NewSearch resets earlier search property values to defaults
    .LookIn = strFolder
    .SearchSubFolders = boolsubfolders
    .fileName = strFiles
If .Execute() > 0 Then
    i = .FoundFiles.Count
    MsgBox "File found = " & .FoundFiles.Count
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE DirectoryList.* FROM DirectoryList;"
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
    For i = 1 To .FoundFiles.Count
        strFiles = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
        strFiles = strFiles & "#" & .FoundFiles(i) & "##Click"
        rst![FileLinks] = strFiles
    MsgBox "No matching File Names found!"
End If
End With

Exit Function

MsgBox Err.Description, , "New_Search"
Resume New_Search_Exit
End Function

If there is any difficulty in running the On Click Event Procedure of the Path Lookup Command Button then replace that with the File Browser method explained in the Article: File Browser in MS-Access (Common Dialog Control).

Download Demo Database

Download Demo DirListing2K.zip



  1. [...] demonstrates an example as how to generate a List of external files in a Listbox as hyperlinks: External Files' List in HyperLinks This may not be an exact solution to your problem but you can modify the program to read the [...]

  2. Your Friend & Partner...

    I really think your blog is great! I've added a link back here; I hope that's alright as I'd like my readers to check your site & articles out. It's Here. Always like to honor high quality content. Great job!...

  3. Hello~ I just downloaded the sample "External Files List in Hyperlinks" db which is a fantastic tool; however, I get a run-time error (type mismatch) when I select "yes" from the "include subfolders" combo box (returning me to "New_Search strtxt, Me!cboYN" in the below procedure. I am running MS Access 2000 on a Windows XP (sp3) machine. Any help is greatly appreciated! acary

    Private Sub cmdGo_Click()
    Dim strtxt As String
    'On Error GoTo cmdGo_Click_Err
    strtxt = Nz(Me![PathName], "")
    New_Search strtxt, Me!cboYN

    Exit Sub

    MsgBox Err.Description, , "cmdGo_Click"
    Resume cmdGo_Click_Exit
    End Sub

  4. The bug is fixed now. You may download the Utility again.

  5. Hello~ First, I would like to say that this DB is a great tool and thank you for sharing it! I should have updated my original question -- shortly after posting I believe I found the bug as well (I changed one of the values in the Y/N drop down from "-" to "-1"). However, I do have another question. I would like the user to simply enter his/her search criteria in the Path Name field, and the "GO" event look to a static location (Y:\General\DHSWiki\) For example, the user enters "news" in the Path Name field and the GO event returns all files located in the DHSWiki folder and subfolder(s) that include "news" anywhere in the filename. Anything you can do to assist is greatly appreciated! Thank you

  6. As a quick solution you may change the middle line of the following Form_Load() event procedure:

    Private Sub Form_Load()
    Me!PathName = CurrentProject.path & "\*.*"
    End Sub


    Me!PathName = "Y:\General\DHSWiki\*.*"

    You may include the Sub-Folder (News) by modifying the above line as Y:\General\DHSWiki\News\*.* and change the combobox setting to YES to include sub-folders under News folder.

  7. Hello again~ I will try your suggestion and post my results. Thank you for responding -- I've been checking a couple of times a day :-)

  8. I am sad to report that it did not work -- I get the following compile error: Expected: line number or label or statement or end of line. Unfortunately, I do not code and cannot resolve the error. Again, any assistance is greatly appreciated. Note: I do not have a subfolder named "news." What I would like to do is provide a field for the user to enter a search word (eg: "news") and the GET FILES" event will return a list of any and all files located in Y:\General\DHSWiki\ (including subfolders therein) with the word "news" anywhere in the filename. Again, any assistance is greatly appreciated :-)


Comments subject to moderation before publishing.

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code