Log In
Register
Home
December 7, 2020
Offline
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 = "\\fs01\Data$\Engineering\Document Control\SA - Eromanga Basin\Growler\PDF" '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
Active Member
January 27, 2023
Offline
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
December 7, 2020
Offline
sorry, where do I put this?
Active Member
January 27, 2023
Offline
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 = "\\fs01\Data$\Engineering\Document Control\SA - Eromanga Basin\Growler\PDF" '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
1 Guest(s)
