Link lookups to corresponding PDFs

[Rob Surina]

I have an Excel 2016 worksheet that cangenerate thousands of possible part numbers that looks something like this==> 6 S1 F1 F1 E1 W1

Additionally, I have thousands of Pdf drawings in file folder G:\PDF with part numbers that looks something like this ==> 6 S1 F1 F1 E1 W1.pdf

I’m looking to add a command button on my worksheet that will bring up theappropriate Pdf drawing from the G:\PDF file folder to Index/match or vlookup the same part numbers that are being generated on the worksheet.

This will allow the user to bring up drawings of the part numbers that arebeing generated on the worksheet by the click of a button.
Is this possible?

 

[cooper645]

How will the end user select the Part number?

Did you want a dynamic dropdown that expands with the growing list of part numbers? This could be laborious to look through, unless you made it searchable. It is however possible.

An easier alternative could be a macro that converts your list of part numbers into hyperlinks and simply apply a filter to the columns, then the user could search by part number / item description etc

 

[cooper645]

If you had a button on your sheet with part numbers listed on it, you could assign the following macro to it.

The following code assumes Part Numbers are listed in column A; from cell A2 to A###

It will simply find the last row of part numbers in column A,
and then make each part number a hyperlink to the File/Folder and part number .pdf, with the friendly name still being the part number

You could then just use filters on your header row so that user can search by part number, description or whatever columns you have etc.

{I have added comments in the code to help you understand what each bit is for}

Option Explicit

Sub InsertLinks()
'https://www.mrexcel.com/forum/excel-questions/1077242-link-lookups-corresponding-pdfs.html


'Decalrations
Dim LastRow As Long, cell As Range


'Error handling for blank rows in range
On Error Resume Next


'Asume a large amount of data, so turn off screen updating
Application.ScreenUpdating = False


'Find Last Row of part numbers (Assuming they are in column A)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row


'Add hyperlink to each cell in the range from assumed range of Part numbers (A1 to A and whatever th lastrow was)
For Each cell In ActiveSheet.Range("A2:A" & LastRow)
    cell.Hyperlinks.Add Range(cell.Address), Address:="G:\PDF\" & cell.Value & ".pdf", TextToDisplay:=cell.Value
Next cell


'Turn Screen updating back on
Application.ScreenUpdating = True
End Sub