Thanks to WESTCONN1 on VBFORUMS here is the answer for those who might be following in my footsteps:
[vba]
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long Private
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private
Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Private Const BM_CLICK = &HF5
Sub GetData() Const DownloadTitle
As String = "File Download"
Const DownloadClass As String = "#32770"
Const ChildTitle As String = "&Open"
Const ChildClass As String = "Button"
Dim hwndParent As Long
Dim hwndChild As Long
Dim lpClassName As String
Dim RetVal As Long
Dim sURL As String
Dim IeApp As InternetExplorer
Dim IeDoc As HTMLDocument
Dim IeECol As IHTMLElementCollection
Dim IeLink As HTMLAnchorElement
With IeApp
.Visible =
True
.Navigate sURL
Do Until .Busy = False And .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
End With
Set IeDoc = IeApp.Document
Set IeECol = IeDoc.getElementsByTagName("A")
For Each IeLink
In IeECol
If IeLink.innerText = "Download to spreadsheet " Then
IeApp.Navigate IeLink.href
' wait for the new instance of IE pops up
Do Until hwndParent
hwndParent = FindWindow(vbNullString, DownloadTitle)
DoEvents
Loop
' get the download dialog
Do Until hwndChildhwndChild = FindWindowEx(hwndParent, ByVal 0&, vbNullString, ChildTitle)
DoEvents
Loop
' make sure we're sitting on the parent
RetVal = SetForegroundWindow(hwndParent)
' wait for the dust to settle - could use SLEEP as alternative
Application.Wait (Now + TimeValue("0:00:02")) ' may need tweeking
' now click on the download dialog
SendMessage hwndChild, BM_CLICK, 0, 0
End If
Next IeLink
IeApp.Quit
Set IeApp = Nothing
Set IeDoc = Nothing
Set IeECol = Nothing
Set IeLink = Nothing
End Sub
[/vba]