Hyperlink Macro

[Solved/Closed]
Report
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010
-
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010
-
Hi
Have a customer profile sheet that has a new customer number each time it is opened. Details are filled in for the customer then a post button is clicked which copies all relevant information to the row with the matching number on a list on another sheet in the same book. Then a Print button is clicked to print the actual profile sheet to PDF and the pdf file is named Customer 'Customer Number' (the reps have these with them when they go to a clients place). What I would like to do is add to the copy and paste macro to add a hyperlink to that matching number on the list page that can then be clicked to open the pdf file that was saved so we don't have to go searching through the folder for the appropriate customer profile to reprint.
Anyone have any ideas?
SLKG

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Presuming this is related to your previous question. This function is based on same. The file name is assumed to be like 700002.pdf

However you can also add the hyper link when data is copied ( as in your previous question). If you want to go with that, the highlighted lines are all you need to add to the previous code


Sub putHyperLink() 

Dim PI_Sheet As String 
Dim DC_Sheet As String 
Dim invoiceLoc As Long 
Dim invoice As Variant 
Dim sFileLoc As String 


    PI_Sheet = "Product Invoice" 
    DC_Sheet = "Data Collection" 
    sFileLoc = "C:\Users\haadi\Documents\" 
     
    Sheets(DC_Sheet).Select 
     
    invoice = Sheets(PI_Sheet).Range("M7") 
    invoiceLoc = 0 
    On Error Resume Next 
        invoiceLoc = Application.WorksheetFunction.Match(invoice, Range("A:A"), 0) 
     
    On Error GoTo 0 

    If (invoiceLoc > 0) Then 
         
        Sheets(DC_Sheet).Hyperlinks.Add _ 
            Anchor:=Sheets(DC_Sheet).Range("A" & invoiceLoc & ":A" & (invoiceLoc + 10)), _ 
            Address:="""" & sFileLoc & invoice & ".pdf""", _ 
            TextToDisplay:="""" & invoice & """" 
             
   End If 

End Sub
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010

That is exactly what I needed rizvisa1!! You are, as ever, a genius!! Thank you!! :)
SLK