Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Hyperlinks

    I have a database of companies. I have created a document table linked to the company table. I also have about 500 documents that I want to have hyperlinked in document table. If I link manually, the link looks like LinkDocumentxxxx.pdf showing the relation of the folder and document. It shows in both the field, and when I edit the hyperlink, it shows as the hyperlink. This is fine.

    I have used a Print Directory program to list the documents in the directory that I want to link to, then manipulated the names in excel so that I have the exact format (linkdocumentxxx.pdf).

    When I import the list into my database, the hyperlink shows blue and underlined, however, a click does nothing. When I edit the hyperlinks, the hyperlink field is blank.

    I was wondering if there was any way to import the data so that it's already linked and I don't have to go through 500 records and set up the link manually. I've already tried tagging it with html, but all that does is show the html in the field, but it still doesn't link it. It sounds like I'm being lazy, but I expect to have to do this to around 4,000 more documents in the next couple of months and any automation of the process would save me.

    I'm using Access 2000 SR1, Windows 2000. Thanks.

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Hyperlinks

    Hi,

    You have to add # to the link to make it valid as HyperLink. Try the code below.

    Sub ValidateHyperLink()
    Dim dbs As Database, rst As Recordset
    Dim I As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblPDFPath")
    If Not rst.EOF Then
    rst.MoveFirst
    For I = 1 To rst.RecordCount
    rst.Edit
    rst("PDFPath") = "Link to '" & rst("PDFPath") & "'" & "#" & rst("PDFPath") & "#"
    rst.Update
    rst.MoveNext
    Next I
    End If
    End Sub

    In the future, you can is the code below to facilitate your work.

    ' Set References to Microsoft Office 9.0 Object Library first
    Private Sub Test()
    Dim dbs As Database, rst As Recordset
    Dim FS As FileSearch, FilePath As String, MyPath As String
    Dim I As Integer, strFileName As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblPDFPath")
    Set FS = Application.FileSearch
    MyPath = "I:"
    strFileName = "*.PDF"
    With FS
    .LookIn = MyPath
    .SearchSubFolders = True
    .FileName = strFileName
    '.FileName = "*.doc"
    If .Execute() > 0 Then
    For I = 1 To .FoundFiles.Count
    Debug.Print .FoundFiles.Count
    FilePath = .FoundFiles(I)
    With rst 'DoCmd.TransferText , , "tblPath", .FoundFiles(i)
    .AddNew
    !PDFPath = FilePath
    .Update
    End With
    Next I
    Else
    MsgBox "There were no files found."
    End If

    End With

    End Sub

    Modify both to fit your need.

    HTH.
    Tim K.

Posting Permissions

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