Event when Excel window is activated

I'm trying to copy data from one program to excel. To save time I want excel to do all the pasting for me.

So, I have one excel workbook open and MS Dynamic NAV open.

I copy the data from NAV and then when I switch to the excel workbook I want it to paste the data in itself.

I've written the code to paste the data in. That works fine, however I can't get this code to fire when I activate or switch to the excel window. I've tried the following but it won't work

Private Sub Workbook_Activate()
    
    Dim pasteBoard As New MSForms.DataObject
    Dim pData As String
    Dim startCell As Range
    Set startCell = Range("D1")
    
    pasteBoard.GetFromClipboard
    pData = pasteBoard.GetText
    
    Do
        Set startCell = startCell.Offset(1, 0)
    Loop Until IsEmpty(startCell)

    startCell.Value = pData

End Sub

And the following as well,

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

    ' 'SAME CODE' '

End Sub

Any thoughts?