Consulting

Results 1 to 2 of 2

Thread: Help Fixing Broken Hyperlinks

  1. #1
    VBAX Newbie
    Joined
    Apr 2017
    Posts
    1
    Location

    Help Fixing Broken Hyperlinks

    I am trying to fix some hyperlinks in a query in an Access '97 database. The database is named "LINK RESTORER.mdb" and the query is named "Q REVISIONS LINK RESTORER". In the query, the 11th column is named "good nhl str-A", and the 12th column is named "NHL-A". The 11th column contains ~3k strings. Each of the strings is a file path to a PDF that I need to be able to link to. The 12th column in the query (formatted as Hyperlinks) is supposed to be the actual link (the 11th column is just a pure string, no hyperlink functionality). When I copy the entire 11th column and paste it into the 12th column, none of the links work. However, when I copy a single string from the 11th column into a single cell in the 12th column, the link works. What I found was that when I right-clicked on a cell in the 12th column and selected the Edit Hyperlink option, the "Link to file or URL:" field is blank when I copy over the whole column at once. However, when I copy a single cell at a time, the "Link to file or URL:" field is populated with the string from the 11th column (this enables the link in the 12th column to work). My question is how can I do this to all the strings in the 11th column without having to manually go through each cell and copy/paste it into the 12th column?

    As an example, I tried copying all the strings from the 11th column into an Excel sheet (I'm much more familiar with VBA in Excel than in Access). The code below worked beautifully, all of the links worked just fine and I could open them without any problems. Would some kind individual help me figure out how to translate the below Excel-based VBA into an Access-based version? Thank you very much in advance.

    Sub HyperCopy()
        Dim r As Range
        
        For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
            r.Hyperlinks(1).Address = r
        Next r
    End Sub

  2. #2
    In Access the hyperlink data type is a actually a text string with three parts separated with a #.


    Example:

    Display Text Here#http://www.hitechcoach.com.com#Sub address here
    When you add data to a Hyperlink field directly, you must include the two # symbols to delimit the parts of the hyperlink data.

    I would use an update query to update Column 12 with the data in Column 11 wrapped with the #.

    Example:
    UPDATE [My Table Name] SET [Column12] = "#" & [Columne11] & "#"

    ALERT: Make a backup of the database before running any Update Query!



    Also look int he help for:
    HyperlinkPart Method
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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