VBA Clicking on an Item with Internet Exporer

My company uses a web based reporting system and I'm trying to automatically export the data to Excel. There is a standard dropdown menu with File > Export to XLS and I would like to automatically click the "Export" button to download the report.

I have read through other posts dealing with clicking buttons to expand a page, or enter a value into a form, etc, but possibly because the button is in a drop down menu, or because the element is within an iframe none of the previously posted solutions have worked for me.

So Far:

  • I can navigate to the page
  • I can get the menuExportXLS element
  • Debug.print prints the correct innerText
  • My code is not throwing any errors

BUT I can't successfully click the button to download the file.

Alternatively, there is a javascript onclick script within the HTML that could trigger the download, but I don't know how to call it in VBA. I would be happy to get either method running.

Sample of HTML

Sub ExportXLSTest()

Dim appIE As InternetExplorerMedium
Dim Obj As Object

Set appIE = New InternetExplorerMedium
sURL = "https://Site"

With appIE
    .Navigate sURL
    .Visible = True
End With

Do While appIE.Busy Or appIE.ReadyState <> 4

Application.Wait (Now + TimeValue("0:00:05"))

Set Obj = appIE.document.getElementsByTagName("iframe")(0).contentDocument.getElementById("menuExportXLS")
Debug.Print Obj.innerText

Set appIE = Nothing

End Sub