1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Hyperlink Help

Discussion in 'Business Applications' started by ns81, Jul 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    I've got a large column of cells, the actual text of which is a relative pathname pointing to .jpg files. All the .jpg files are in the same directory.

    The cells look something like this:

    pics\FID1.jpg
    pics\FID2.jpg
    pics\FID3.jpg
    etc..
    etc...
    etc....

    The whole file was exported from another program and the cells are mere text. I would like to be able to tell excel to make the text of each cell into a hyperlink since the cell text is already pointing to a file name. This way when it is clicked the corresponding picture will open without me having to right click the cell, choose hyperlink and manually change the pathname. Is this possible?

    Thanks,
    Nick
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    make a copy of your sheet and try this code out....


    Sub hyperlink()


    'It will loop down the column change the cell to a hyperlink (skips over empty cells)
    '
    ' hyperlink Macro
    7/18/2007 by Ziggy Stouten
    '
    Dim hyper As String ' hyperlink variable
    '
    Dim endrow As Integer
    Dim RowX As String ' Cell to change
    Dim RowC As Integer 'row Counter

    Range("A65536").End(xlUp).Select

    RowC = 1

    endrow = ActiveCell.Row

    Do Until RowC > endrow

    RowX = "A" & RowC 'Modify Letter for Column

    Range(RowX).Select

    If Range(RowX).Value <> "" Then ' skips cell if blank

    hyper = Range(RowX).Value
    Range(RowX).Hyperlinks.Add Anchor:=Selection, Address:= _
    hyper _
    , TextToDisplay:= _
    hyper

    End If


    RowC = RowC + 1

    Loop

    End Sub


    I attached the book with the code I made and a button if you don't know VBA, you could just copy/paste your column into column A and convert it and then past it back if you want.
     

    Attached Files:

  3. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    Sorry I'm not brand new to excel but I'm no expert either! Can you give me a quick rundown of how to run that code on the sheet?

    Thanks,
    Nick
     
  4. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    Sorry, I didnt catch the txt attachment the first time... I'll give that a try
     
  5. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    Yes I snuck it in when you weren't looking :) I can tell you how where to put it, but I think you will be able to use the attachment for what you need.

    If this is going to be an ongoing thing, where you continue to have to do it with new lists then I would suggest a different place for the code...

    Just tell me what the process is.
     
  6. ns81

    ns81 Thread Starter

    Joined:
    Jul 11, 2007
    Messages:
    32
    That worked perfectly. You saved me from a lot of tedious work. This should be a one time thing, but I'll remember it if I have to use it again. Thanks a lot for your help.

    Nick
     
  7. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    no problem, it was a good question :)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/597400

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice