Hyperlink Filename of Column A To same file names in a Directoy

[mubashiraziz]

Hi to every one,

I am having a problem while hyperlinking bunch of files. I have a macro which takes the name of files from a directory and then copy in Excel Column and Hyperlink them.
But now I want to macro to check file names from Excel file Columns, search in the Directory and Hyperlink them. I can't use hyperlink() function because i have to submitted this bunch to client and they don't want to change the path.

Column A

PROJ-13-BC-01-0601-1.pdf

PROJ-13-BC-01-0601-2.pdf

PROJ-13-BC-04-0601-1.pdf

PROJ-13-BC-04-0602-1.pdf

PROJ-13-BC-08-0601-1.pdf

PROJ-13-BC-08-0601-2.pdf

PROJ-13-BC-08-0601-3.pdf

PROJ-13-BC-08-0602-1.pdf

PROJ-13-BC-08-0603-1.pdf

PROJ-13-BC-08-0604-1.pdf

PROJ-13-BC-08-0605-1.pdf

PROJ-13-BC-08-0606-1.pdf

PROJ-13-BC-08-0607-1.pdf

PROJ-13-BC-08-0608-1.pdf

PROJ-13-BC-08-0609-1.pdf

PROJ-13-BC-08-0610-1.pdf

PROJ-13-BC-08-0610-2.pdf

<colgroup><col></colgroup><tbody>
</tbody>

pdf files are on below path
D:\Drawing

I want macro to compare file names from column A to Directory and then hyperlink.

I have tried by best to tell my problem and will wait for experts suggestion.

I have posted my problem on below forum as well:
https://www.excelforum.com/excel-programming-vba-macros/1207128-hyperlink-cell-values-to-pdf-file-in-a-directoy.html#post4776592

<tbody>
</tbody>

 

[mubashiraziz]

I ve got the awesome solution from below link

https://www.mrexcel.com/forum/excel...file-local-directory-based-cell-contents.html

 

[admiral100]

Hi,

The code checks if a file actually exists in a specific directory and if it does the it generate an hyperlink.
It also put in column B (YES/NO) whether the file exists .


Sub CheckIfFileExistsThenHyperLink()


Dim LRow As Integer
Dim LPath As String
Dim LExtension As String

LRow = Range("A1").End(xlDown).Row
LPath = "D:\Drawing"

While CStr(LRow) > 0


If Len(Range("A" & CStr(LRow)).Value) = "" Then

Else

If Len(Dir(LPath & Range("A" & CStr(LRow)).Value)) = 0 Then
Range("B" & CStr(LRow)).Value = "No"

Else
Range("B" & CStr(LRow)).Value = "Yes"
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & CStr(LRow)), Address:=LPath & Range("A" & CStr(LRow)).Value


End If
End If


LRow = LRow - 1


Wend


End Sub