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)
'Stop
If FileName = VBA.Constants.vbNullString Then
MsgBox "File: " & FileName & " Does Not Exist" & vbNewLine & _
"Please Run the Monarch Automation Again..."
Else
src5.Sheets("WFB_StatementDetail").Activate
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
Display More
Hello All:
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.
Any ideas???
Thanks for any help.