December 7, 2020
I currently have this code below and it does nothing.
I have concated file names in column A.
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
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.
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)