I currently have this code below and it does nothing.
I have concated file names in column A.
example "OPS-GRO15-PR-HAC-001"
In my folder as per below, I have all the PDF file for each document number in column "A", but they may have a revision number on the end of the doc number
example OPS-GRO15-PR-HAC-001_r1
I need the code to look for the matching file name without the revision number on the end as this changes all the time and to link the PDF file to correct corresponding document number in column "A". hope this makes sense.
I don't know much about VBA, so I don't know what I need to do to fix the below code.
Sub AddHypaerlinks()
Dim lastRow As Long
Dim myPath As String, fileName As String
myPath = "\fs01Data$EngineeringDocument ControlSA - Eromanga BasinGrowlerPDF" 'SET TO WHERE THE FILES ARE LOCATED
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("I" & i), myPath & Dir(fileName)
End If
Next
End Sub
Hi,
Before getting into the macro ... it would seem to me you need to handle the revision string ...
=SUBSTITUTE(yourText,RIGHT(yourText,3),"")
Hope this will help
sorry, where do I put this?
Sorry ... my answer should have been clearer ...
Sub AddHyperlinks()
Dim lastRow As Long
Dim myPath As String, fileName As String
' example "OPS-GRO15-PR-HAC-001" in Column A
myPath = "\fs01Data$EngineeringDocument ControlSA - Eromanga BasinGrowlerPDF" 'SET TO WHERE THE FILES ARE LOCATED
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
fileName = myPath & Replace(Range("A" & i).Value, Right(Range("A" & i).Value, 3), "") & "*.pdf"
If Len(Dir(fileName)) > 0 Then ' IF THE FILE EXISTS THEN
ActiveSheet.Hyperlinks.Add Range("I" & i), myPath & Dir(fileName)
End If
Next i
End Sub
Hi Sorry for the late reply, I added the code above and it appears not to do anything.
