PDA

View Full Version : Auto Hyperlinking VBA that auto matches?



binar
07-13-2015, 07:33 PM
Fellow Forum Members,
I would be very grateful if someone can help me in developing a VBA that automatically generates a hyperlink list that auto matches. Let me elaborate. I have 400 PDF in this directory:

C:/art/schematic01.pdf
C:/art/schematic02.pdf
C:/art/schematic03.pdf (plus 397 more PDF file names)

In addition, in "Column A" of my Excel file I have a list of 55 PDF files I need hyperlinked to only 55 (out of 400) PDF files located in my "C:/art" folder. My "Column A" Excel list looks like this:

A
1 schematic 13.pdf
2 schematic 45.pdf
3 schematic 30.pdf (plus 52 rows of more PDF file names)

However, what I want to avoid is having to manually match 55 file names for creating 55 hyperlinks to the PDF files located in my "C:/art" folder. Is it possible for a VBA to automatically create the 55 hyperlinks for me that automatically does the matching for me between my list in "Column A" and 55 out of 400 PDF files located in my C:/art folder? Any help in developing such a VBA will be greatly appreciated. Thanks in advance.

binar
07-13-2015, 08:45 PM
Fellow Forum Members,
Below is some code I tested with no success. My "Column A" header title is "PDFS". How do I get the code below to Hyperlink to list of PDF files in directory and match with Excel row entry in Column 1 ? Any help in tweaking the code below will be appreciated. By the way, can it be done with a formula? Again any help will be appreciated. Thanks.




Sub Create_Hyperlinks()

Dim folder As String
Dim DocID As Range

folder = "C:\Root\PDFS"

For Each PDFS In Range("A1", Cells(Rows.Count, "A").End(xlUp))
PDFS.Hyperlinks.Add Anchor:=PDFS, Address:=folder & PDFS.Value & ".pdf", TextToDisplay:=PDFS.Value
Next

End Sub