Create cell value as Hyperlink if Value matched with the pdf file name in a specific directory.

Hi,

I have a large datasheet where AA9 to AA1200 have cells (Not all Cell currently have Value)

I have directory E:\Bill\ where the pdf is located.

The cell value and the pdf file name are the same.

If the Cell value is matched with the pdf file name then the cell value creates the hyperlink.

If nothing matched then skip.

If New file is created and a new entry is made in between AA9:AA1200 then auto-matched & creates the hyperlink.

Any help will be appreciated.

Thanks in Advance.

Hi, I found a Solution and It worked

Sub AddHypaerlinks()

Dim lastRow As Long
Dim myPath As String, fileName As String


myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

    fileName = myPath & Range("A" & i).Value & "*.pdf"
    
    If Len(Dir(fileName)) <> 0 Then 'IF THE FILE EXISTS THEN
    
        ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
    
    End If
    
Next

End Sub

Thanks.

Hi,

Need help.

The above code creates a hyperlink of cell-matched data from a folder if the pdf file exists.

But if the cell in the excel file is blank then it also creates a random link to the existing pdf file.

How to ignore this.

If the cell is blank no hyperlink.

Only hyperlink of matched data.

Any help apricated .

Thanks