VBA code for finding matching files and add hyperlink

[papplaszlodaniel]

Hi there,


I have the list of file names in Excel (MS Excel 2013) without extensions (such as ".pdf").
I would like to have a macro which if finds a file with a matching file name (in a given folder), it hyperlinks it to the matching Excel cell.


E.g.:

In Excel, column B contains the following list:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]File names[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[/TR]
</tbody>[/TABLE]

In a folder there are the following files:

B2.pdf
B3.pdf
D444.pdf
B5.pdf
B6.pdf

So after running the macro, it links the matching ones to the relevant cells (and does nothing when it doesn't find exact matches):

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]File names[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[/TR]
</tbody>[/TABLE]


It would be the best if when running the macro, a window would pop up to select the folder where the macro should check the files.