CODEWITHSUNDEEP
excelvba
YaraK
YaraK

Reputation: 47

How to copy a hyperlink to a cell with only the path?

I created a code that uses a for loop to open a list of workbooks and copy the data from the opened workbook to the active workbook.

I have figured out how to copy the data however I want to add a hyperlink for each workbook. The path to each file is found in the strPath string.

I want to assign the hyperlink to the cell :

sheet.Cells(index, dateColumn) (note that date column is just the column number I find thanks to the FindDate function.)

Here is what I have so far: 

For index = 3 To 37 
    dateColumn = FindDate(portfolioDate)
    portfolioName = ActiveSheet.Range("A" & index & "").Value & ".xls"
    strPath = "G:\Risk\Risk Reports\VaR-Stress test\" & portfolioDate & "\" & portfolioName & ""

    Set wb = Workbooks.Open(strPath)     
    sheet.Cells(index, dateColumn).Value = wb.Close Savechanges:=False
Next index

Upvotes: 0

Views: 51

Answers (1)

Gangula
Gangula

Reputation: 7284

It looks like your syntax is wrong.

Please try the following at the Set: 

Set wb = Workbooks.Open(strPath)     
    sheet.Cells(index, dateColumn).Value = strPath
With sheet 
   .Hyperlinks.Add Anchor:=.Cells(index, dateColumn), _ 
   Address:=strPath, _ 
   ScreenTip:="Path to the file", _ 
   TextToDisplay:="File Path to " & "\" & portfolioName & portfolioDate ' Change strPath as per your Requirement to display a different text
End With
wb.Close Savechanges:=False

But i'm not sure if the sheet in your code is a variable.

Upvotes: 2

Related Questions