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.

Hyperlinking Macro

Discussion in 'Business Applications' started by tgreen, Dec 20, 2010.

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

    tgreen Thread Starter

    Joined:
    Dec 20, 2010
    Messages:
    4
    Hi,
    I am trying to create a macro to create hyperlinks within an excel file. The macro should be able to create the hyperlink based on the field entries from column C and add each individual file name to the pathway in the hyperlink, each hyperlink should be on a separate line all the way down to the bottom of my data, if this is possible anything would help....thank you!
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
  3. tgreen

    tgreen Thread Starter

    Joined:
    Dec 20, 2010
    Messages:
    4
    Ziggy,

    I am trying to extract the data from column C to Create the hyperlink with the pathway to where my tickets are stored locally on my computer such as "C:\AAPROJECT\Tickets" then the hyperlink for Row 1 should be ":C:\AAPROJECT\Tickets\BK967564.pdf" and inserted into Column X. Each row varies in numbers so you wouldn't be able to increase by 1, whatever is in Column C would need to be added to the end of the pathway I specified above, I hope this gives you a better ideal, thanks a lot for the help I truly do appricate it.
     

    Attached Files:

  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I can't download your sample (at work), but here is code based on what you said. Do you know where to put it? You haven't said when it should trigger... eg whenever you save?


    Code:
    Sub hyperlink()
    '
    ' hyperlink Macro
    '  7/18/2007 by Ziggy 
    '
    Application.ScreenUpdating = False
    
    Dim hyper As String  ' hyperlink variable
    '
    Dim endrow As Integer
    
    Dim RowC As Integer 'row Counter
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    'checks 3rd column for last used row number
    endrow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    
    
    RowC = 1
    
    
    
    Do Until RowC > endrow
    
    
               
              ' selects column# 25 which is X ( needs to select to insert Hyperlink)
        Cells(RowC, 24).Select
        
        If Cells(RowC, 3).Value <> "" Then ' skips cell if blank
        
        hyper = "C:\AAPROJECT\Tickets\" & Cells(RowC, 3).Value
          Cells(RowC, 3).Hyperlinks.Add Anchor:=Selection, Address:= _
            hyper _
            , TextToDisplay:= _
            hyper
             
             End If
             
      
    RowC = RowC + 1
    
    Loop
            
                   
    Application.ScreenUpdating = True
                   
                   
    End Sub
    
     
  5. tgreen

    tgreen Thread Starter

    Joined:
    Dec 20, 2010
    Messages:
    4
    Ziggy1,
    The Macro appears to look fine but when I click on it gives me an error "Cannot open the specified file" so I used a batch maker to create the .pdf extension to the end the data in column C and that didn't work either not sure where the error could be my pathway to the .pdf appears to be correct. Any ideas?
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    you need to verify the link, look closely to make sure all the "\"
    slashe are in place....it works for me?
     
  7. tgreen

    tgreen Thread Starter

    Joined:
    Dec 20, 2010
    Messages:
    4
    Okay thank you so much, you saved me a lot of time! :D
     
  8. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    awesome!:cool:
     
  9. 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/969646

  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