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 hyperlink 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 and they all end in .PDF. They're also all located in 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?

 

[jeffcoleky]

how could I make it to open the pdf and not the folder only?

I didn’t understand the answer much and that’s why I am the OP, but from what I can gather in your post, you need to include a filename in the link and not just a folder.

 

[llleo]

you dont need macro, the formula is
=HIPERLYNK("C:\PDFS" & SUBSTITUTE(A1;" ";"") &".pdf";A1)

Can anyone give an example this formula with bit more details, to me its a bit unclear as to what is what.......


Would appreciate a quicker response.

 

[nathanmav]

patel45 has given you the solution.

Just thought I'd share this modification, which I use when moving my file to other PCs, emailing etc.
In this case you would have the PDFS folder in the same folder as your excel file and move the main folder.

=Hyperlink( "." & "PDFS" & "" Substitute(A1," ", "") & ".pdf", A1)

Hi nithin89

I'd Like to ask about your formula what if there is a subfolder or the pdf file is stored in the server because i tried your formula its not working for me.

Example: \\192.168.1.200\Main Folder\subFolder 1\ subFolder 2\subFolder 3\subFolder 4\001-xx-xxx_00.pdf

Thank you