Grant Grant - 6 months ago 45
HTML Question

Print an HTML file with VBA

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.

Answer

You need to use InternetExplorerMedium instead of InternetExplorer, as in this post: http://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