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.
- A Table: DirectoryList with a single Field FileLinks with data type Hyperlink.
- A Datasheet Form created on the above Table with the name FilesListing_Sub.
- The Main Form FilesListing with the Datasheet Form inserted as Sub-Form that occupies the major part of the design.
- 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.
- 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.
- 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.
- 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.
- The Command Button with the name cmdClose and with the Caption Close when clicked closes the FilesListing Main Form.
- 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:
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).