Link Listing to Individual Files

[RYL]

Hello I am needing some help. I have a listing of invoice numbers in Excel. For each invoice listed I have a corresponding PDF document that I want linked to the Excel Cell (could have thousands of rows). PDF are saved. Is there an easy way to use VBA code to link cells to PDF? Thank you.

 

[AC PORTA VIA]

Try this

Sub CREATE_hyperlink_FROM_LIST()

    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim xFilename As String
    Dim i As Long
    Dim lastRow As Long
    
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)

    With xFiDialog
        .InitialFileName = Application.DefaultFilePath & "\" 'change as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
    xPath = .SelectedItems(1) & "\"
    End With
    lastRow = Cells(Rows.Count, "A").End(xlUp).row
    
    For i = 2 To lastRow
        xFilename = Dir(xPath & Cells(i, "A").value & "*.pdf", vbNormal)
        If Len(xFilename) > 0 Then
            ActiveSheet.Hyperlinks.Add Cells(i, 1), xPath & xFilename, , , xFilename
      
        End If
    Next i
    
    
End Sub

 

[RYL]

Try this

Sub CREATE_hyperlink_FROM_LIST()

    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim xFilename As String
    Dim i As Long
    Dim lastRow As Long
   
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)

    With xFiDialog
        .InitialFileName = Application.DefaultFilePath & "\" 'change as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
    xPath = .SelectedItems(1) & "\"
    End With
    lastRow = Cells(Rows.Count, "A").End(xlUp).row
   
    For i = 2 To lastRow
        xFilename = Dir(xPath & Cells(i, "A").value & "*.pdf", vbNormal)
        If Len(xFilename) > 0 Then
            ActiveSheet.Hyperlinks.Add Cells(i, 1), xPath & xFilename, , , xFilename
     
        End If
    Next i
   
   
End Sub

THANK YOU!

 

[AC PORTA VIA]

You are welcome, glad it works for you