Enter Hyperlink based on file name

[Panoos64]

Hi to all of you, I would much appreciate if you could provide me a support so that, to create a VBA code which should run through col. “J” in my list file and where a cell is blank should place the hyperlink based on matching file name with name in col. “F” Therefore that the file name contains creation date at the end so should match the name only. Note that “List file” and “Items cards” are in same folder. Below I present an extract of my list file after the run of code. Thank you all in advance

F G H I J

Item description	Dpt​	Item code​	Category​	Invetory card
Wholemeal slices 1000gr	Restaurant​	40107001​	Bakeries​	Wholemeal slices 1000gr 15.03.2023.xlsx

 

[GSP75]

Sub InsertHyperlink()

Dim listFile As Workbook
Dim listSheet As Worksheet
Dim itemsFolder As String
Dim itemsFile As String
Dim itemRow As Long

Set listFile = ActiveWorkbook 'Change to the name of your list file
Set listSheet = listFile.Sheets("Sheet1") 'Change to the name of your sheet
itemsFolder = listFile.Path & "\" 'Assuming the items files are in the same folder

For itemRow = 2 To listSheet.Cells(Rows.Count, "J").End(xlUp).Row 'Loop through all the rows with inventory cards
If listSheet.Cells(itemRow, "J").Value = "" Then 'Check if the cell is empty
itemsFile = Dir(itemsFolder & "*" & listSheet.Cells(itemRow, "F").Value & "*.xlsx") 'Find the file name with matching date
If itemsFile <> "" Then 'Check if a matching file was found
listSheet.Hyperlinks.Add Anchor:=listSheet.Cells(itemRow, "J"), Address:=itemsFolder & itemsFile 'Insert the hyperlink
End If
End If
Next itemRow

End Sub

This code assumes that your list file is open and active when you run it. If your list file has a different name or your sheet has a different name, you'll need to change the code accordingly. Also, make sure that the items files have the format "Item description creation date.xlsx" and that the date is separated from the description by a space.

 

[Panoos64]

Thank you GSP, it works and is based on my data. Thank you for your support too and your time. Hv a great day!