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? Several queries of different kind 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.


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.


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 Files' 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 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 Form has a simple design as you can see from the image given above. 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 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 to 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.


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 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:


Create 3D Headings on Forms
Border 2D Heading Text
Border 3D Heading
Shadow 3D Heading Style.



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 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 Sub-Routines and Function which runs behind the Main Form FilesListing is 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
Me.FilesListing_sub.Form.Requery

cmdGo_Click_Exit:
Exit Sub

cmdGo_Click_Err:
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
    
cmdPathLookup_Click_Exit:
Exit Sub

cmdPathLookup_Click_Err:
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
    .NewSearch
    .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
        rst.AddNew
        strFiles = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
        strFiles = strFiles & "#" & .FoundFiles(i) & "##Click"
        rst![FileLinks] = strFiles
        rst.Update
    Next
Else
    MsgBox "No matching File Names found!"
End If
End With

New_Search_Exit:
Exit Function

New_Search_Err:
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 - File: DirListing2K.zip (Size:36.6K)

---

Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray
Text Box and Label Inner Margins
Multiple Parameters for Query
Form Menu Bars and Toolbars