MASS HYPERLINKS TO FILE PATHS

[markkuznetsov1]

I have a list of about 10000 parts...each of these parts should have a .pdf file in a folder on my desktop. If I could get the text for the file path in all the cells going down. I.E. "D\parts\PN1000\PN1000.PDF". How can I do a mass change to convert the text value to hyperlinks? I want to quickly scroll through to open the file and see what it is.

 

[Jeffrey Mahoney]

If you want to try something different. This method allows you to just put the Path and File name in a cell and double click the cell to open the file. I don't like Hyperlinks because they change all the time.

The macro below is trying to look at a range called HyperLinkArea. You will need to create that named range to include the cells with the Path\FileName. You can also use to open folders only.

Add this to the Sheet Module level in VBA.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim R As Range
    Dim HyperlinkStr As String
    Dim i As Range
    
    Set R = Range("HyperLinkArea")
    Set i = Intersect(R, Target)
    If Not i Is Nothing Then
      HyperlinkStr = i.Value
      If HyperlinkStr <> "" Then
        Cancel = True
        OpenPathFile HyperlinkStr               'Path, FileName
      End If
    End If
    
    
End Sub

Add this to a Standard VBA Module

Sub OpenPathFile(PathFile As String)

  Dim PF As Variant
  
  If PathFile = "" Then Exit Sub
  PF = PathFile & vbNullString
  CreateObject("Shell.Application").Open PF
  

End Sub