Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink to Files (Access 97)

    is there a way to import a inventory of files in a directory to a table & be able to hyperlink ?

    I can drag & drop individual files to an access feild record, but want to be able to fill
    the table with the contents of a directory containing files that i wish to hyperlink to. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to Files (Access 97)

    Yes, using VBA code. Activate the Modules tab of the database window, and click New. Copy the following code into the code module, replacing the dummy names I used by the ones you have.

    Sub ImportFileList()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strPath As String
    Dim strFile As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    ' substitute name of table in line below
    Set rst = dbs.OpenRecordset("tblFilenames", dbOpenDynaset)

    ' substitute path in line below, with trailing backslash
    strPath = "C:MyFolder"

    ' if necessary, change file filter in line below, e.g. "*.doc"
    strFile = Dir(strPath & "*.*")

    ' loop through files
    Do While Not (strFile = "")
    rst.AddNew
    ' substitute field name in line below
    rst.Fields("FileName") = strPath & strFile
    rst.Update
    strFile = Dir
    Loop

    ExitHandler:
    ' clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    Exit Sub

    You can run this code (after saving the module) by clicking somewhere in it and pressing F5. If the field in the table is actually a hyperlink field, change the line

    rst.Fields("FileName") = strPath & strFile

    to

    rst.Fields("FileName") = "#" & strPath & strFile & "#"

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    (<!t>[tab]<!/t> tags inserted by HansV to preserve indentation)

    Thanks for the code,..I get an error " Item not found in this collection "

    I must be doing something wrong. I an not familiar with working with Modules,
    I placed the code within a on_click command button..

    Private Sub ImportFileList_Click()
    Dim dbs As DAO.DATABASE
    Dim rst As DAO.Recordset
    Dim strPath As String
    Dim strFile As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    ' substitute name of table in line below
    'Set rst = dbs.OpenRecordset("tblFilenames", dbOpenDynaset)
    Set rst = dbs.OpenRecordset("Hyperlink", dbOpenDynaset)

    ' substitute path in line below, with trailing backslash
    'strPath = "C:MyFolder"
    strPath = "C:Invoices"

    ' if necessary, change file filter in line below, e.g. "*.doc"
    strFile = Dir(strPath & "*.*")

    ' loop through files
    Do While Not (strFile = "")
    rst.AddNew
    ' substitute field name in line below
    'rst.Fields("FileName") = strPath & strFile
    rst.Fields("FileName") = "#" & strPath & strFile & "#"
    rst.Update
    strFile = Dir
    Loop

    ExitHandler:
    ' clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    Exit Sub
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to Files (Access 97)

    Check the name of the field very carefully. It should be exactly equal to the name of the field in the table. Is "FileName" really the name of the field in the "Hyperlink" table?

    I tested the code (with the name of a folder on my PC) and it ran without problems.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    It works!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    forgot to change ref to feild name..
    'rst.Fields("FileName") = strPath & strFile
    rst.Fields("Location") = "#" & strPath & strFile & "#"

    thanks again

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    It works!!!

    forgot to change ref to feild name..
    'rst.Fields("FileName") = strPath & strFile
    rst.Fields("Location") = "#" & strPath & strFile & "#"

    thanks again

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    Works..

    But now will be nice to a "Explorer" type interface to select directory to import file hyperlinks.

    I am asking way too much .. ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to Files (Access 97)

    Yes, that is possible.

    Copy the following function into a standard module, or into the module behind the form (the module that contains the On Click procedure):

    Public Function BrowseFolder(Optional Title As String = "Select a Folder", Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
    End Function

    and replace the line

    strPath = "C:Invoices"

    by these:

    strPath = BrowseFolder
    If strPath = "" Then
    Exit Sub
    Else
    strPath = strPath & ""
    End If

    If you want to start in a particular drive or folder, use something like BrowseFolder(, "C:") instead of just BrowseFolder.

    Note: the BrowseFolder function was originally posted by <!profile=Don Ceraso>Don Ceraso<!/profile>.

  9. #9
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    Getting strange data..
    I am getting strange data..

    can you explain the syntax ..
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path

    I was expecting an explorer type window to pop-up..

    What can i expect when I run this code?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to Files (Access 97)

    The code creates a low-level system object (Shell.Application) and calls the BrowseForFolder method. More than that I cannot tell you. It should display an Explorer-type window. It seems unlikely that your system doesn't provide it (Who's Online reports that you're on Internet Explorer 6). Are you sure you call it the way I indicated in my previous reply?

    I have attached a database (Access 97, zipped) with an alternative. Open the form, click the button and see if it works for you. If so, copy the module modBrowseFolder to your database, and remove the BrowseFolder function you copied from my previous reply.

    Change the line

    strPath = BrowseFolder

    to

    strPath = BrowseFolder("Select a Folder")
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Apr 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to Files (Access 97)

    your MDB example works!

    You are the best !

    Looks like I will be able to make it work with your previous hyperlink routine.

    Thanks again

    Bob McVicar
    ISE Research Phone: 604-942-5223 Ext.266
    1734 Broadway St. Fax: 604-942-7577
    Port Coquitlam, B.C. e-mail bob.mcvicar@ise.bc.ca
    Canada, V3C 2M8 web www.ise.bc.ca

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •