Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

External Files List in Hyperlinks

Introduction

Is it possible to display a listing of different file types from Disk as Hyperlinks on Access Form and open them in their parent Applications?  Several queries of this kind have been received in my emails from readers of the earlier Articles.  This is about using File Brower (the Common Dialog Control) and using Hyperlinks in Microsoft Access.

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 the Files List Form.

    To answer the above queries we will create a Form with a Datasheet Sub-Form and with a few simple controls to take a listing of all frequently used files of your choice (Text Files, Word Files, Excel Files, or  Files of all Types) from the Disk and display them in a list of Hyperlinks. When you would like to open and work with a file in their parent Application, simply click on the hyperlink to select and open the file.  An Image of the sample Form is given below:

    Finding Files on Folders

    Click on the Create File Links Button to open the File Browser (the Common Dialog Control). Browse and find your file(s), select one or more files, and click the OK button to bring the file references into the above List control as Hyperlinks.

    The Files Path Name is displayed to the right side control of the Hyperlink. This will help in finding the location of frequently used files of Excel, Word, PDFs, or whatever file files you create a link for. It takes only a few clicks of the mouse and we are doing it plenty of times a day.

    You may bring in files in batches and all of them will be added to the directory-list Table.

    The Data Sheet 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 two Fields: 1. FileLinks with data type Hyperlink. 2. Path with a Text data type to store the file’s complete path name.

    2. The Datasheet Form was created in the above Table with the name FilesListing_Sub.

    3. The Main Form FilesListing with the Datasheet Form inserted as a Sub-Form that occupies the major part of the design.

    4. A Command Button (Name: cmdFileDialog) with the Caption Create File Links runs the Common Dialog Control to browse and select files from the Disk and insert them as Hyperlinks in the FileLinks Field of Table.

    5. The Field Path will be updated with the location address of the selected files.

    6. The Unbound Text Box below will show the Current Project Path as the default location when the File Dialog is open.

    7. The Command Button with the name cmdDelAll and with the Caption Delete All Links when clicked deletes all file links from the tables.

    8. The Command Button with the name cmdDelLink and with the Caption Delete One Link when clicked deletes the selected hyperlink item from the List.  You can manually delete one or more Links, select them by holding the Shift key down and clicking on the left border of items next to each other, and press the DELETE Key.

    9. Command Button with the Caption Delete File on Disk and the name cmdDelFile deletes the selected Link and the file on disk.  Only one File can be deleted at one time.  Use this option with caution, once the file is deleted from the disk it cannot be reversed.  Click at the left border of a link to select it and click on the Delete File on Disk.

Managing the Files List

The Files' List is created as Hyperlinks in the target table: DirectoryList.  Links can be added to the list and incoming links are appended to the existing records. You cannot add a record manually or edit a record on the Datasheet (you may do so directly to 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 segments) 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 page and try it out before you design one of your own to understand how it works.

You can easily implement this in your various Projects by simply importing the Forms and the Table into your Projects if required. The demo database is an Access2007 Version file.

The Main Form Class Module Code

The VBA Code, which runs behind the Main Form FilesListing is given below for info.

Option Compare Database
Option Explicit
Dim strpath As String

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

Private Sub cmdDelFile_Click()
On Error GoTo cmdDelFile_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("File: " & strFile & vbCr & "DELETE from Disk?", _
vbQuestion + vbYesNo, "cmdDelFile_Click") = vbYes Then
   If MsgBox("Are you sure you want to Delete" & vbCr _
   & rst!Path & " File from DISK?", vbCritical + vbYesNo, "cmdDelFile_Click()") = vbNo Then
    GoTo cmdDelFile_Click_Exit
   End If
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    If Len(Dir(strFile)) > 0 Then
    Kill strFile
    MsgBox "File: " & strFile & " Deleted."
    Else
      MsgBox "File: " & strFile & vbCr & "Not Found on Disk!"
    End If
End If
Else
    MsgBox "File: " & strFile & " Not Found!!"
End If

cmdDelFile_Click_Exit:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

cmdDelFile_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelFile_Click()"
Resume cmdDelFile_Click_Exit
End Sub

Private Sub cmdHelp_Click()
DoCmd.OpenForm "Help", acNormal
End Sub

Private Sub Form_Load()
'strpath = CurrentProject.Path & "\*.*"
On Error GoTo Form_Load_Err
GetProperty
strpath = Me!PathName

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & " : " & Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

Private Sub cmdDelLink_Click()
On Error GoTo cmdDelLink_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("Link: " & strFile & vbCr & "DELETE from above List?", _
vbQuestion + vbYesNo, "cmddelLink_Click()") = vbYes Then
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    MsgBox "File Link: " & strFile & " Deleted."
End If
Else
    MsgBox "Link: " & strFile & " Not Found!!"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

cmdDelLink_Click_Exit:
Exit Sub

cmdDelLink_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelLink_Click()"
Resume cmdDelLink_Click_Exit

End Sub

Private Sub cmdFileDialog_Click()
On Error GoTo cmdFileDialog_Click_Err
'Requires reference to Microsoft Office 12.0 Object Library.
Dim db As DAO.Database, rst As DAO.Recordset
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
Dim strfiles As String
   'Clear listbox contents.
   'Me.FileList.RowSource = ""

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = True
      .InitialFileName = strpath
            
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "All Files", "*.*"
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "Excel WorkBooks", "*.xlsx; *.xls; *.xml"
      .Filters.Add "Word Documents", "*.docx; *.doc"

      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True 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
        For Each varFile In .SelectedItems
        rst.AddNew
        strfiles = Mid(varFile, InStrRev(varFile, "\") + 1)
        strfiles = strfiles & "#" & varFile & "##Click"
        rst![FileLinks] = strfiles
        rst![Path] = varFile
        rst.Update
    Next
Me.DirectoryList.Form.Requery
         'Loop through each file selected and add it to the list box.
         'For Each varFile In .SelectedItems
            'Me.FileList.AddItem varFile
         'Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

cmdFileDialog_Click_Exit:
Exit Sub

cmdFileDialog_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Click_Exit
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Len(strpath) = 0 Then
  strpath = "C:\My Documents\*.*"
End If
SetProperty
End Sub

Private Sub PathName_AfterUpdate()
'On Error GoTo PathName_AfterUpdate_Err
Dim str_path As String, i As Long
Dim test As String

    Me.Refresh
    str_path = Me!PathName
    i = InStrRev(str_path, "\")
    str_path = Left(str_path, i) & "*.*"
    strpath = str_path
    
    test = Dir(strpath)
    If Len(test) = 0 Then
        MsgBox "Invalid PathName: " & strpath
        strpath = CurrentProject.Path & "\*.*"
        Me.PathName = str_path
        Me.Refresh
        Exit Sub
    End If
    Me.PathName = strpath
    Me.Refresh
    
PathName_AfterUpdate_Exit:
Exit Sub

PathName_AfterUpdate_Err:
MsgBox Err & " : " & Err.Description, , "PathName_AfterUpdate()"
Resume PathName_AfterUpdate_Exit
End Sub

Private Function GetProperty() As String
On Error GoTo GetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = doc.Properties("defaultpath").Value
If Len(strLoc) = 0 Then
   strLoc = CurrentProject.Path & "\*.*"
End If

strpath = strLoc
Me!PathName = strpath
Me.Refresh

GetProperty_Exit:
Exit Function

GetProperty_Err:
MsgBox Err & " : " & Err.Description, , "GetProperty()"
Resume GetProperty_Exit
End Function


Private Function SetProperty() As String
On Error GoTo SetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = Me!PathName
If Len(strLoc) = 0 Then
    strLoc = CurrentProject.Path & "\*.*"
End If
doc.Properties("defaultpath").Value = strLoc

SetProperty_Exit:
Exit Function

SetProperty_Err:
MsgBox Err & " : " & Err.Description, , "SetProperty()"
Resume SetProperty_Exit
End Function

Download Demo Database

Download Demo DirListing2K1.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

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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