- Sub ActivateHLinks()
- Dim FileName As String
- Dim RctX As Long
- Dim src5 As Workbook ' THE Invoice WORKBOOK.
- Dim Rct3 As Long
- Dim Cct3 As Long
- Dim CAddr3 As String
- Dim Rgx3 As String
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- FileName = "L:\" & Mid(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 4, 4) & "\" & _
- ThisWorkbook.Sheets("Sheet2").Cells(2, 9) & "\" & _
- Left(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 8) & "WFB_StatementDetail.xlsx"
- Set src5 = Workbooks.Open(FileName, True, True)
- If FileName = VBA.Constants.vbNullString Then
- MsgBox "File: " & FileName & " Does Not Exist" & vbNewLine & _
- "Please Run the Monarch Automation Again..."
- RctX = Cells(Rows.Count, 9).End(xlUp).Row
- For Each myCell In Range("I2:I" & RctX)
- ActiveSheet.Hyperlinks.Add myCell, myCell.Value
- Next myCell
- End If
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
I have an Excel file with several hundred calculated hyperlinks using a formula link: HyperLink Calculation ("=HyperLink("+"L:\Folder\SubFolder\Filename.jpg+"). I found a vba code snippet: ActiveSheet.Hyperlinks.Add myCell, myCell.Value, which seems to activate the formulas (full vba code attached), but which I click on the resulting hyperlink I get error saying the target file cannot be found.
Without running the VBA script, I can open the same file, double click each cell and press enter and the hyperlink works as expected. The code is not making any change other that activating the hyperlink. Also note that the cell still shown the full formula (i.e., the =hyperlink part is still visible. When I click inside the cell value and press enter, the =hyperlink part is no longer visible, but I get the same error. Is seems something is there that causing the problem, but I cannot determine the problem.
Thanks for any help.