Print an HTML file with VBA

Question

I have been researching this for some time and I have come up with the following code. However, I keep getting an error. See below.
The intent of this code is to produce an HTML document. Then it opens and prints the document from a printer. I have gotten the file to save successfully and even open it in an IE window. Then I get the error.

Function generateResults()
    Dim resultsBrowser As SHDocVw.InternetExplorer
    Set resultsBrowser = New SHDocVw.InternetExplorer
    Dim resultsPath As String
    Dim resultsFile As String

    resultsPath = ThisWorkbook.Path & "\As-Run Test Results"

    If Len(Dir(resultsPath, vbDirectory)) = 0 Then
        MkDir resultsPath
    End If

    resultsFile = resultsPath & "\As-Run " & Format(Now, "mm-dd-yyyy hmmss") & ".html"

    Open resultsFile For Output As #1
    Print #1, "<html><title>Test</title><body>Hello World</body></html>"
    Close #1

    resultsBrowser.Navigate resultsFile

    Do While resultsBrowser.ReadyState = READYSTATE_COMPLETE
    Loop

    resultsBrowser.Stop

    resultsBrowser.ExecWB 7, 1

    resultsBrowser.Quit

    Set resultsBrowser = Nothing
End Function

Here is the error message.

Error Message

When I go to debug, the VBA debugger points to the following line:

resultsBrowser.ExecWB 7, 1

What am I doing wrong? My research shows that this works for others, but it does not seem to work for me. Another oddity is that if I navigate to about:blank instead of the HTML file and comment out the ReadyState check loop, a blank Print Preview comes up successfully.

I'm guessing you want to say `Do While resultsBrowser.ReadyState <> READYSTATE_COMPLETE`. Otherwise, I can confirm that this code works on my end. Can you say anything more about your environment? Under what context are you running this? — owenfromcanada, May 27 '16 at 17:54
You need to use `InternetExplorerMedium` instead of `InternetExplorer`. — ib11, May 27 '16 at 18:10

score 0 · Accepted Answer · edited May 23 '17 at 12:15

You need to use InternetExplorerMedium instead of InternetExplorer, as in this post: https://stackoverflow.com/a/19221313/6201755

(As a note, to make this clear for future users, the SHDocVw object is contained in the Microsoft Internet Controls reference library, make sure to add this reference in the VBE.)

Also as the comment above, this Do While resultsBrowser.ReadyState = READYSTATE_COMPLETE line makes it loop infinitely, so you need to change it to <>.

Here is the working code:

Private Sub test()

    Dim resultsBrowser As SHDocVw.InternetExplorerMedium
    Set resultsBrowser = New SHDocVw.InternetExplorerMedium
    Dim resultsPath As String
    Dim resultsFile As String

    resultsPath = ThisWorkbook.Path & "\As-Run Test Results"

    If Len(Dir(resultsPath, vbDirectory)) = 0 Then
        MkDir resultsPath
    End If

    resultsFile = resultsPath & "\As-Run " & Format(Now, "mm-dd-yyyy hmmss") & ".html"

    Open resultsFile For Output As #1
    Print #1, "<html><title>Test</title><body>Hello World</body></html>"
    Close #1

    resultsBrowser.Navigate resultsFile

    Do While resultsBrowser.ReadyState <> READYSTATE_COMPLETE
    Loop

    resultsBrowser.Stop

    resultsBrowser.ExecWB 7, 1

    resultsBrowser.Quit

    Set resultsBrowser = Nothing

End Sub

Thanks. This worked perfectly. Thanks for the catch on the infinite loop. I had actually been running with that commented out in case it was causing an issue. Probably why I did not notice. — Grant, May 27 '16 at 19:54

Print an HTML file with VBA

1 Answers1