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?

 

[patel45]

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

 

[Momentman]

Maybe

=HYPERLINK("C:\PDFS\" & SUBSTITUTE(A1," ","")&".pdf")

and drag down for all other cells

 

[nithin89]

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)

 

[jeffcoleky]

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)

Thank you both!

One reason I needed this was because even after creating the hyperlinks, often times Excel will "auto-update" the links and change the reference path on the hard drive. This is quite frustrating so I thought that if I had a macro I could run it and it would RE-Link all the files in column "A" properly.

nithin89: Could your formula be modified so that no matter where the XLSM file is run from, it always looks in the subdirectory "PDFS"? That way if the XLSM file is in c:\run\, it would look in c:\run\pdfs. Or if the XLSX were opened from c:\users\test\desktop it would look in c:\users\test\desktop\pdfs.

Those are great formulas as they are but if you think they could be improved I'd appreciate the help!

 

[nithin89]

The formula already looks in the subdirectory 'PDFS' (based on your excel file's location). So it will work in both the situations you have mentioned exactly as you require.

I am not sure whether that fully answers your question, because I don't know how to deal with the auto-update issue.

 

[jeffcoleky]

The formula already looks in the subdirectory 'PDFS' (based on your excel file's location). So it will work in both the situations you have mentioned exactly as you require.

I am not sure whether that fully answers your question, because I don't know how to deal with the auto-update issue.

That's a great formula and exactly the effect I need to take place.

Do either of you know how to convert this formula to a macro which would be applied to the highlighted cells?

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

 

[Jerry Sullivan]

I've responded to your question here...
http://www.mrexcel.com/forum/excel-...uto-hyperlink-selected-cells.html#post3271829

 

[Rbveiga]

Hello,
I use the formula mentioned aboce in my file and the hyperlink opne the olfer and not the pdf.

=HYPERLINK("L:\FASE\2000_Account\2100\Revenue\GL pipe recon\00- 2017\02",R23)

my file have the same name as i have in column Q

 

[Rbveiga]

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