Using VBA to auto generate a hyperlink to a different workbook

I already have a Sub routine that can pull data from several workbooks in a specific folder and populate a table in a ‘Dashboard’ workbook. But I also want it to generate a hyperlink to the workbook as well so you can easily access it without needing to go to the folder and find the workbook manually.

Also I have very limited experience in VBA so obvious things may not be so obvious with me.

I recorded a macro of me manually inserting a link to the file and got the following

ThisWorkbook.Worksheets("Data").Cells(i + 5, 27).ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="Logs\CM350C3.xlsx" _ , TextToDisplay:="Logs\CM350C3.xlsx"

Now the Sub routine that populated the table with data already has a function that reads the file name so my plan was to replace the fixed file name with the file name of the file it was currently pulling data from

ThisWorkbook.Worksheets("Data").Cells(i + 5, 27).ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="File.Name" _ , TextToDisplay:="File.Name"

However, when I run this it I get a runtime error 438 and a message saying "Object does not support this property or method".

I tried changing the Anchor:= to Range:= and providing a cell reference but it didn’t work either.

I also tried to use the =HYPERLINK formula with =HYPERLINK("[CM350C3]Sheet1!A1", "CLICK HERE")to create a link but I couldn’t get the formula working. Kept coming up with a message saying "Cannot open specific file".

I also tried the hyperlink formula but specified the full file path and file name. This did work outside of the VBA script but I’m not sure how to get the script to automatically write it into the cells using the file name. I tried following the format I’m using for populating the table with the data.

ThisWorkbook.Worksheets("Data").Cells(i + 5, 25) = "Hyperlink Formula" but kept running into Object errors.

Any assistance would be appreciated.

>Solution :

You can use this Sub:

Public Sub writeHyperlinkForFilename(cTarget As Range, Fullfilename As String)
With cTarget
    .Value = Chr$(187) & " " & .Value  'adds a >> in front of the path name --> you can remove this - it's just a visual sign.
    If .Hyperlinks.Count > 0 Then .Hyperlinks.Delete
    .Parent.Hyperlinks.Add Anchor:=cTarget, Address:=Fullfilename, ScreenTip:="Opens file " & vbCrLf & Fullfilename
End With
End Sub

and call it e.g. like this

writeHyperlinkForFilename ThisWorkbook.Worksheets("Data").Cells(i + 5, 25), "Logs\CM350C3.xlsx"

But I think you will need a full path not a relative path … so you might have to adjust the last parameter.