Batch Hyperlink Macro for PDF files

Afternoon all,

First time poster here looking for a little help with adding hyperlinks into my excel workbook.

I have a column of PDF numbers which correlate exactly to the filename except having .pdf at the end. I have a range of say 1001 to 1100 that I would like to create into hyperlinks in the following location:

S:\Komfort Product Range\Standard Product Range\600 Series\Junction Drawings\1001.pdf

So I know how the hyperlink function would work ... i.e.

=Hyperlink("S:\Komfort Product Range\Standard Product Range\600 Series\Junction Drawings\1001.pdf", "1001")

Could someone suggest a macro that would be able to run on say 100 rows using the same file location (junction drawings) but putting the cell value down as the extension so as to open the hyperlink, and also put the cell value as the display text, in this case 1001

Many thanks for any help and if this is possible, have search on here and a few other websites but can't seem to find anything related to using a cells value for a hyperlink when opening external files!

Hope I'm not breaking any rules here by double posting rather than editing my O.P, please correct me if wrong.

I have found a code that works for me but isn't writing the hyperlink as a function rather inserting it a similar way if we used control+k or right click>hyperlink.

[code]
Sub test()
Dim MyPath, MyName
MyPath = "S:\Komfort Product Range\Standard Product Range\Polar 100\Junction Drawings\"
MyName = Dir(MyPath, vbNormal)
Do While MyName <> ""
If Right(MyName, 4) = ".pdf" Then
For Each cel In Range("c1:c2000")
If cel.Value & ".pdf" = MyName Then
ActiveSheet.Hyperlinks.Add Anchor:=Range(cel.Address), Address:=MyPath & MyName
End If
Next cel
End If
MyName = Dir
Loop
End Sub
[\code]

How can we edit this code so that excel writes the hyperlink as a =Hyperlink() function ?