Link to home
Start Free TrialLog in
Avatar of KentDBerry
KentDBerry

asked on

VSTO 2005 SE: Design Question pertaining to the Workbook_SheetFollowHyperlink event in an Excel workbook

I need to migrate the below code that is contained in an Excel 2003 Workbook to Excel 2007 using best practices for VSTO 2005 SE.  Note that the below VBA code is triggered when a user clicks on a hyperlink field in the document.  The Workbook_SheetFollowHyperlink event fires and then calls  ieFindAndScroll  passing some text to be found on a website.

Since I will be removing all this VBA code from within the excel document, I need a way to provide this same functionality using VSTO 2005 SE.  I already have an Excel Add-In for other functionality and know a bit about this technology.  

My main question is how to trap the hyperlink trigger using my Excel Add-In code.  Any thoughts?


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    ieFindAndScroll Trim(Target.Name)
End Sub
 
Code below in a separate module
 
Dim ie As InternetExplorer
Dim strYear As String
Dim strLastYearSearched As String
 
Public Function ieFindAndScroll(strCurrentProtocolName As String)
    On Error GoTo endit
    Dim bFound As Boolean
    
    strYear = Left(Right(strCurrentProtocolName, 6), 4)
    If IsNumeric(strYear) = False Then
        MsgBox "Protocol " & strCurrentProtocolName & " must be renamed using the standard format (i.e. ending in -2008US) in order to search the Protocol Documents website!", vbInformation + vbOKOnly
        Exit Function
    End If
    
beginning:
    If ie Is Nothing Then
        Set ie = New InternetExplorer
        ie.Navigate "http://nafta1.pro.intra/brd/TT/FieldCrops/" & strYear & "/Protocols/SortedByProtocol_Study.html"
        Do Until ie.ReadyState = READYSTATE_COMPLETE
        Loop
        strLastYearSearched = strYear
    Else
        If strYear <> strLastYearSearched Then
            ie.Navigate "http://nafta1.pro.intra/brd/TT/FieldCrops/" & strYear & "/Protocols/SortedByProtocol_Study.html"
            Do Until ie.ReadyState = READYSTATE_COMPLETE
            Loop
            strLastYearSearched = strYear
        End If
    End If
 
    Set txt = ie.Document.body.createTextRange()
    bFound = txt.findText(strCurrentProtocolName)
    If bFound = True Then
        txt.MoveStart "character", -1
        txt.findText strCurrentProtocolName
        txt.Select
        txt.ScrollIntoView
        ie.Visible = True
    Else
        MsgBox "Protocol " & strCurrentProtocolName & " was not found on the " & strYear & " Protocol Documents website!", vbInformation + vbOKOnly
    End If
    
    Exit Function
endit:
    Select Case Err.Number
        Case -2147417848 'automation error.  user probably closed ie manually
            Set ie = Nothing
            GoTo beginning
        Case Else
            Debug.Print Err.Number & " " & Err.Description
             MsgBox Err.Number & Err.Description
    End Select
End Function

Open in new window

SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KentDBerry
KentDBerry

ASKER

ok I'll give this a try and get back with you.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial