Hyperlink via VBA

[Balmer07]

Hi,

I currently have a 'parts' spreadsheet, in column 'A' I have recorded all of the part codes. In a folder on my drive I have the drawings to all of these parts. What I want to do is hyperlink the drawings to the part code in column 'A' of the spreadsheet, there are over 300 parts to this so I was hoping there was a speedy way of doing this in VBA (note the drawings have the same file name as the code recorded in the spreadsheet)

Any ideas?

Thanks

Stevie B

 

[Nine Zero]

Try this out

Sub Hyper()
Dim r As Range
'Change to your range here
For Each r In Range("A1:A5")
'Make sure your sheet is placed here
With Worksheets("Sheet1")
'Change this to the path where your images are, if your file names in 
'in the column A already contains the extension then you dont need the last part 
.Hyperlinks.Add r.Offset(, 1), "C:\Users\You\Desktop\" & r.Value & ".jpg"
End With
Next r
End Sub

Let me know if you need anything else

 

[Balmer07]

Hey,

So this code worked, however some files have a revision number which is not included on the spreadsheet so I need to search for filenames that contain the data which is in the cell.

I searched the forums and came across a similar thread to this. Below is code it detailed which worked perfectly for what I needed.

Sub AddHyperlinks()


Dim lastRow As Long
Dim myPath As String, fileName As String


'SET TO WHERE THE FILES ARE LOCATED
myPath = "(your file path")
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

fileName = myPath & Range("A" & i).Value & "*.pdf"

If Len(Dir(fileName)) <> 0 Then 'IF THE FILE EXISTS THEN

ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)

End If

Next


End Sub