getting html source with excel-vba?

Question

i would like to direct an excel VBA form to certain URL's and get the html source and put it in a string. is this possisble?

Answer 1

Yes. One way to do it is to use the MSXML dll - and to do that you need to add a reference to the "Microsoft XML" library via Tools->References.

Here's some code that displays the content of a given URL:

Public Sub ShowHTML(ByVal strURL)

    On Error GoTo ErrorHandler

    Dim strError As String
    strError = ""

    Dim oXMLHTTP As MSXML2.XMLHTTP
    Set oXMLHTTP = New MSXML2.XMLHTTP

    Dim strResponse As String
    strResponse = ""

    With oXMLHTTP

        .Open "GET", strURL, False

        .send ""

        If .Status <> 200 Then
            strError = .statusText
            GoTo CleanUpAndExit
        Else

            If .getResponseHeader("Content-type") <> "text/html" Then
                strError = "Not an HTML file"
                GoTo CleanUpAndExit
            Else
                strResponse = .responseText
            End If

        End If

    End With


CleanUpAndExit:

    On Error Resume Next ' Avoid recursive call to error handler

    ' Clean up code goes here

    Set oXMLHTTP = Nothing

    ' Report any error

    If Len(strError) > 0 Then
        MsgBox strError
    Else
        MsgBox strResponse
    End If

    Exit Sub


ErrorHandler:

    strError = Err.Description

    Resume CleanUpAndExit

End Sub