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

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 Demo DirListing2K.zip



Share:

8 comments:

  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 [...]

    ReplyDelete
  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!...

    ReplyDelete
  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
    Me![FilesListing_sub].Form.Requery

    cmdGo_Click_Exit:
    Exit Sub

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

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

    ReplyDelete
  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

    ReplyDelete
  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

    to

    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.

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

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

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

User-Defined Data Type-3

Last week we have learned how to define a User -Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go t...

Labels

Blog Archive

Recent Posts