VBA insert hyperlink in an excel file

[Panoos64]

Hi to all of you!
Please note that i have a folder name "EMPLOYEES DETAILS" which contains an excel file EMPLOYEES SUMMARY 2023.xlsx and for each employee it's file e.g. EMPLOYEE 60201356 25/03/23.xlsx. I am kindly require to provide me a support so that to run a VBA code, which should check through col. "J" in file EMPLOYEES SUMMARY 2023.xlsx and if is blank, should place as hyperlink the employees personal details EMPLOYEE 60201356 25/03/23.xlsx if match the name and date of employment in col. "B". I present below an extract of original data in schedule 1. and the expected result in schedule 2.
Thank you all in advance!

Schedule 1.

Schedule 2.

 

[zero269]

Hi to all of you!
Please note that i have a folder name "EMPLOYEES DETAILS" which contains an excel file EMPLOYEES SUMMARY 2023.xlsx and for each employee it's file e.g. EMPLOYEE 60201356 25/03/23.xlsx. I am kindly require to provide me a support so that to run a VBA code, which should check through col. "J" in file EMPLOYEES SUMMARY 2023.xlsx and if is blank, should place as hyperlink the employees personal details EMPLOYEE 60201356 25/03/23.xlsx if match the name and date of employment in col. "B". I present below an extract of original data in schedule 1. and the expected result in schedule 2.
Thank you all in advance!

Schedule 1.
View attachment 104088


Schedule 2.
View attachment 104089

Ello Panoos64,

To help improve your chances of getting help in this forum, do the following:

Use this forums Excel Add-in to simplify sharing your sample data; it's located here. There also a link to it in the default reply window at the bottom of any thread:

Update your profile to include your Office Version, Power Tools (if applicable) and OS Platform:

To update your profile, just click on your profile name at the top and you'll see those options available to you.

I'm in the process of providing a potential solution now. Just verifying a few things first before posting it.

 

[zero269]

Hi to all of you!
Please note that i have a folder name "EMPLOYEES DETAILS" which contains an excel file EMPLOYEES SUMMARY 2023.xlsx and for each employee it's file e.g. EMPLOYEE 60201356 25/03/23.xlsx. I am kindly require to provide me a support so that to run a VBA code, which should check through col. "J" in file EMPLOYEES SUMMARY 2023.xlsx and if is blank, should place as hyperlink the employees personal details EMPLOYEE 60201356 25/03/23.xlsx if match the name and date of employment in col. "B". I present below an extract of original data in schedule 1. and the expected result in schedule 2.
Thank you all in advance!

Schedule 1.
View attachment 104088


Schedule 2.
View attachment 104089

Ello Panoos64,

I'm not sure which version of Office you're using, so I'm providing two options:

Column C is using TEXTJOIN (O365) and Column D is using CONCAT (all versions) to combine the cell values.

TEXTJOIN or CONCAT are combining string values and cell values of Column B & A.
TEXT is converting the DATE to TEXT using a 'yyyy-mm-dd' FORMAT. Windows doesn't allow slashes "/" in file or folder names as seen in your example.
The two STRINGS being combined are a sample file path "C:\Employee Files\" at the beginning and the ".xlsx" file extension.

Once all of the above are displayed correctly and we see a full path to the file, we can then wrap all that up in the HYPERLINK Function.
You can add a friendly name as well for the hyperlink, so I used:

TEXTJOIN(,TRUE,B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx")
CONCAT(B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx")

See below the sheet for the full formula.

I also used a Lastname, Firstname approach, but you obviously want to change anything needed to conform with your standard file naming convention.

If needed, you will likely need to change the date format from yyyy-mm-dd to dd-mm-yyyy as seen in your sample data.

Be sure to make a copy of the Sheet and test it before implementing these changes in your production file.

VBA Testing.xlsm
	A	B	C	D
1			Requires Office 365	Any Office Version
2	Date Employed	Name	Employee File using TEXTJOIN	Employee File using CONCAT
3	2023-03-25	Lname, Fname 60201356	Lname, Fname 60201356 2023-03-25.xlsx	Lname, Fname 60201356 2023-03-25.xlsx
4	2023-01-16	Lname, Fname 60201408	Lname, Fname 60201408 2023-01-16.xlsx	Lname, Fname 60201408 2023-01-16.xlsx
Hyperlinks

Cell Formulas
Range	Formula
C3:C4	C3	=HYPERLINK(TEXTJOIN(,TRUE,"C:\Employee Files\",B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx"),TEXTJOIN(,TRUE,B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx"))
D3:D4	D3	=HYPERLINK(CONCAT(B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx"),CONCAT(B3," ",TEXT(A3,"yyyy-mm-dd"),".xlsx"))

 

[Panoos64]

Thank you zero269! The first formula C3:C4 adapt to my data and works perfect. I use the office 2021 and thank for your advice to update my profile. I was searing for a long time about this project and i appreciated so much that, you resolved my issue. Thank you once again for your time. Have a great day!

 

[zero269]

Thank you zero269! The first formula C3:C4 adapt to my data and works perfect. I use the office 2021 and thank for your advice to update my profile. I was searing for a long time about this project and i appreciated so much that, you resolved my issue. Thank you once again for your time. Have a great day!

That's great news and I'm glad this worked out for you.
Best regards,