Copy/Paste Link dependant on name in different column

I have 500+ rows in a single worksheet. I need to update every row with a new column (in the middle of all existing columns). Column A contains unique names. The new column needs to be a link to another workbook within the same folder. The names of the workbooks will match the name within column A. Rather than opening each workbook, and performing a copy -> paste special -> paste Link into each of the rows, or a Duplicate, and changing the name, is there a way to create a Macro/VBA code to do this? The link will be similar to this, with a different WorkBookName on each row.

Please Login or Register  to view this content.

The number of rows in my workbook can be added to and deleted as needed, but the files themselves will not be deleted out of the folder. Any suggestions would be great.

Thank you in advanced. ~Amy