Hyperlink to a PDF in Pivot table

[Tory]

Hello - I have read the forum threads about using VBA code to utilize hyperlink in pivot tables and I am still confused. I have a set of data which has hyperlinks:

=HYPERLINK("\\spfps\Projects\__MANUFACTURED PARTS\S-20.pdf", "S-20")

I put the data into a pivot table and I get the friendly name S-20 and the hyperlink is gone. How do I utilize VBA code to make the link work?

Thanks.

Tory

 

[Chihiro]

Welcome to the forum. Please have read of link below.
https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/

Without sample workbook that mirror your actual workbook in set up, with desensitized data. It is difficult for us to give you help.

Also, have a read of thread below and see if you can adopt the code. The code is somewhat specific to OP's need, but you will also see links to other sites that has other approach to same issue.

https://chandoo.org/forum/threads/h...-hyperlinks-in-pivot-table.29085/#post-175189

 

[Chihiro]

Also, alternate to above. If you have Excel 2013 or later and PowerPivot, PowerQuery and PowerView, this can be done without VBA.

However, this method will not allow use of friendly name and will require full path as hyperlink displayed value.

 

[Tory]

Than you so much for the response. I have read the other threads and I am not good at VBA - I struggled understanding the code. Attached is my example. Data dump is on the data tab. Column D has hyperlinks. I would like to see the pivot table carry the link as well.

THANK YOU!

 

[Chihiro]

Pivot table will not be able to carry hyperlink.

Therefore, what the code has to do, is check for value of selected pivottable cell and find it in "Data" sheet, Column D. And then read HyperLink formula, and follow the path stored there.

Below code goes in Worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pvt As PivotTable
Dim c As Range
Dim haddress As String
Set pvt = Target.Parent.PivotTables(1)
If Target.Count > 1 Then Exit Sub
If Target.Value = "Part #" Then Exit Sub
On Error GoTo ErrHandle:
If Not Intersect(Target, pvt.RowRange) Is Nothing Then
    haddress = GetLink(Target)
    Application.DisplayAlerts = False
    If Len(haddress) > 0 Then _
        ThisWorkbook.FollowHyperlink Address:=haddress, NewWindow:=True
End If

ErrHandle:
If Err.Number <> 0 Then
    MsgBox "Unable to follow HyperLink. Check HyperLink formula"
End If

    Application.DisplayAlerts = True
End Sub

And below function in Standard module.

Public Function GetLink(rng As Range) As String
Dim c As Range
Dim mLink As String
With Sheet3.Columns(4)
    Set c = .Find(What:=rng.Value, After:=.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart)
    If Not c Is Nothing Then
        If InStr(1, c.Formula, "Hyperlink", 1) Then
            mLink = Split(c.Formula, Chr(34))(1)
        Else
            mLink = ""
        End If
    End If
End With
GetLink = mLink
End Function

See attached.

 

[Tory]

You are the best! I cannot thank you enough! This is awesome.

Thank you so much for your time.

Tory

 

[Piet Ghesquiere]

Hi,

when i try the above for pictures, i get the error every time.
is there something different i have to do in the code?

i want to let the picture (which is stored on a local C drive) pop up, when i click mounting stud in colum pictures (PIVOT)

see file for more clarity,
Thank you in advance for your expertise

 

[Acombe]

Hi Chihiro,

I have applied the above code to my own circumstance and it is working quite well - so thank you! It was difficult to find any tutorials on getting hyperlinks to PDFs to work within a pivot table.

I was wondering if you would you be able to explain the lines of code above for how they each work? I might be able to better apply it that way.

Thanks again.