Hyperlink all cells in Column "A" to a file on local directory based on cell contents?

[jeffcoleky]

I have this data, and a lot more just like it I need to automatically add insert hyperlinks for using a macro:

[TABLE="width: 104"]
<tbody>[TR]
[TD]File[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134189[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134174[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134176[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134187[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134186[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134812[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134188[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134190[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134191[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134184[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134185[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134179[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134173[/TD]
[/TR]
[TR]
[TD="align: left"]DN 2012 134175[/TD]
[/TR]
</tbody>[/TABLE]

I need each line to link to the matching PDF on the local drive. The file names do NOT have spaces in them. They all end in .PDF and are located n the same folder under c:\PDFS

The above filenames would then be respectively linked to these files via hyperlink:

    C:\PDFS\DN2012134189.pdf    C:\PDFS\DN2012134174.pdf    C:\PDFS\DN2012134176.pdf    C:\PDFS\DN2012134187.pdf    C:\PDFS\DN2012134186.pdf    C:\PDFS\DN2012134812.pdf    C:\PDFS\DN2012134188.pdf    C:\PDFS\DN2012134190.pdf    C:\PDFS\DN2012134191.pdf    C:\PDFS\DN2012134184.pdf    C:\PDFS\DN2012134185.pdf    C:\PDFS\DN2012134179.pdf    C:\PDFS\DN2012134173.pdf    C:\PDFS\DN2012134175.pdf

Does anyone know how to automatically create these links using a macro?

 

[Gary's Student]

Hi-light the cells that you want to convert and run this small macro:

Sub HyperVerter()
Dim DQ As String, r As Range
DQ = Chr(34)
P1 = "=HYPERLINK(" & DQ
P2 = DQ & "," & DQ
P3 = DQ & ")"
For Each r In Selection
    s1 = r.Text
    s2 = "C:\PDFS\" & Replace(s1, " ", "") & ".pdf"
    r.Formula = P1 & s2 & P2 & s1 & P3
Next
End Sub