Automation of IE via VBA executes as intended, but throws an automation error

Question

I have the following code, which is supposed to open Internet Explorer in order to download a file.

Sub hentRapport()
  Dim IEapp As Object
  Dim WebUrl As String
  
  Set IEapp = CreateObject("InternetExplorer.Application") 'Set IEapp = InternetExplorer
  WebUrl = Oversikt.Range("Adresse")
  
  With IEapp
    .Silent = True 'No Pop-ups
    .Visible = True 'Set InternetExplorer to Visible
    .Navigate WebUrl 'Load web page
  
    'Run and Wait, if you intend on passing variables at a later stage
    Do While .Busy
        DoEvents
    Loop
    
    Do While .ReadyState <> 4
        DoEvents
    Loop
  End With
End Sub

Internet Explorer (IE 11.0.9600.17691) opens as expected, and I get up the dialog for downloading the file, but at the same time I get an error from the macro:

The error happens on the line

Do While .ReadyState <> 4

and I can't figure out why. Doesn't that line simply state that Excel doesn't need to wait for IE to do its thing before accepting other input?

---

Found a solution

After a lot googling, I finally came across this page, which contained a solution:

The issue is related to IE8 and the Protected Mode: On feature. The purpose of this is to prevent malicious software from being run but it also prevents legit VBA code from operating. Depending on your work enviorment, disabling this may not be an option. Not to mention the general risks associated with doing that.

I recommend the following solution.

Instead of using:

Set ie = CreateObject("InternetExplorer.Application&qu ot;)

use:

Set ie = New InternetExplorerMedium

You will need to add a reference to Microsoft Internet Controls.

Now I just hope that all my users have that library :D

Answer 1

How about something quicker? Using an API function that I believe IE is using, in order to download a file.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long


Public Function DownFromWeb(strURL As String, strFile As String) As Boolean
    Dim ret As Long

    ret = URLDownloadToFile(0, strURL, strFile, 0, 0)

    If ret Then
        MsgBox "Failed to download file", vbExclamation
    End If

    DownFromWeb = (ret = 0)
End Function

Public Sub TestDownload()
    Const URL As String = _
        "https://www.gravatar.com/avatar/eab3ce1e413f1043da3a1574a0ab7360?s=24&d=identicon&r=PG&f=1"

    Dim sDstFolder As String
    Dim sFullPathFileName As String

    ' get the destination of the temp folder.
    ' it should give you the path like you do in windows:
    ' Start-> Run-> %temp%
    sDstFolder = Environ("temp")

    ' add a "\" at the end of the destination path as needed
    sDstFolder = sDstFolder & IIf(Right(sDstFolder, 1) = "\", "", "\")

    ' combine the path with a file name to your choosing
    sFullPathFileName = sDstFolder & "360.png"

    ' This will try to delete the previously downloaded file( just in case)
    On Error Resume Next
    Kill sFullPathFileName
    On Error GoTo 0

    If (DownFromWeb(URL, sFullPathFileName) = True) Then
          ' Open the file (probably a windows popup will appear):
          Shell "explorer " & sFullPathFileName
          ' OR
          ' if it is an excel file, you could do:
          ' Workbooks.Open(sFullPathFileName).Activate
    End If
End Sub

!!! By the way, if all you need is to download and open an excel file, office has a built in option to do so (also with doc files in word). In such a case you might "get away" with a one liner like so:

Workbooks.Open("http://highlycited.com/highly_cited_2001.xlsx").Activate

Answer 2

Excel is not recognising the state specified. If you change the expected return it should run fine.

While .document.ReadyState <> "complete"
  'loop
Wend

See more Here