'Important:  in your VB project, set a reference to Microsoft Internet Controls (c:windowssystem32shdocvw.dll)

Option Explicit

' macro to test the payslip viewer routine
 Sub test_show_payslip()

    Dim address As String, pers_number As String
    
    address = ""           'a) add the payslips file path and name eg "U:/payslips data/payslips.html"
    pers_number = ""       'b) add a test personnel number eg "77777"

    Call html_payslip_viwer(address, pers_number)

End Sub

'Shows one payslip in an internet explorer
 Sub html_payslip_viewer(address As String, pers_number As String)
    
    Dim objIE, strBody, lngStartPos, strAllPayslips    'declare  variables
    Dim chars_before_pernr As Long, chars_after_pernr As Long
    
    
    'set start and end point of one payslip relative to the personnel number
    chars_before_pernr = -1000       'c)adjust this number until the start of each payslip is correct
    chars_after_pernr = 30000        'd)adjust this number until the end of each payslip is correct
    
    ' Create InternetExplorer Object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Toolbar = False    ' turn off the toolbar (optional)
    objIE.MenuBar = False    ' turn off the statusbar (optional)
    
    ' navigate to the payslips file for all employees
    objIE.Navigate address
    ' pass back the html file as a string
    strAllPayslips = objIE.Document.body.innerhtml
 
    ' find the start position of the personnel number in the html string
    lngStartPos = InStr(1, strAllPayslips, pers_number) + chars_before_pernr
    ' if the personnel number is found then
    If lngStartPos > 0 Then
    ' find the end position of that persons payslip
      strBody = Mid$(strAllPayslips, lngStartPos, chars_after_pernr)
    Else
    ' otherwise send a message that no payslip could be found
      strBody = "Unable to find a payslip for personnel number " & pers_number & " in file " & address
    End If
   
    ' pass back the html for one payslip
    objIE.Document.body.innerhtml = strBody

    ' wait while the internet explorer screen loads
    Do While objIE.Busy
    Loop
 
    objIE.Visible = True   'make the internet explorer screen visible

End Sub

'this subroutine creates a hyperlink in the first column of the worksheet
Sub CreateHyperlinks()
   Dim rngRange As Range, rngCell As Range

   Set rngRange = ActiveSheet.Cells(1, 1).CurrentRegion

   For Each rngCell In rngRange.Columns(1).Cells  '<< modify the column number if personnel numbers aren't in the first column
     rngCell.Hyperlinks.Add Anchor:=rngCell, address:="", SubAddress:=rngCell.address, ScreenTip:="view payslip"
   Next rngCell
End Sub