VBA – Acquiring data from Web Pages and Web Services using WinHTTP

Ok, web is here, is there, is everywhere. Fortunately, VBA is able to get information from web pages and services using libraries available on Windows. WinHTTP is the library we are going to use to give to our VBA applications the power to get and send data to web using the HTTP protocol.

WinHTTP, as you might expect, has methods and functions to create requests based on HTTP, so, we are talking about HTTP VERBS: GET, POST, PUT, DELETE and so on. There are other, the these four are the main ones. For this article, we going to use the GET verb to create a simple request to a web page.

Even if you never heard about it, you use it all the time when um request a web page on your browser. When we use GET, it means “GET”. In HTTP context, GET something means, almost 100% of the times, get a web page. This is what happens when you type an address on your web browser and type enter.

Ok, let’s see this happens on VBA code:

Sub http()
    Dim MyRequest As Object
 
    'coloca a instância do WinHTTP na memória
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", "http://www.tomasvasquez.com.br"
 
    'Faz a requisição
    MyRequest.Send
 
    'obtém o conteúdo da resposta e coloca na janela de verificação imediata 
    Debug.Print MyRequest.ResponseText
 
End Sub

Well, what is exactly the same as I described earlier, but where are not in the browser. So, what we have is the HTML content of the page requested, in this particular case, my home page. 🙂

If you visit it right now and try to check the source code of the page (usually available when you press the right button of the mouse), you can see is the same we have in the Immediate Window. Well, it’s a big HTML, as you can check in the image below.

vba_winhttp_tomasvasquez

At this point, you probably figured out some possibilities for this approach. We can get any content available on the web, from web sites to content, like images and documents (ok, it’s is a quite different, but you can start from here). But, the most common usage for this kind of request is used it to call REST based services. If you are a developer, you’ve might heard a lot about it recently. And if you already needed to call one, you know exactly what you have to do from now on.

To finish this article, I’m going to improve last code to do something useful. The macro below gets the feed content from my forum and print it to Immediate Window. The feed is RSS base, so, I needed to do add some stringing code.

Sub UltimasDoForum()
    Dim MyRequest As Object, _
    resposta As String, _
    respostaParcial As String, _
    retorno As Long, _
    stringTitulo As String, _
    fimTitulo As Long
 
    'coloca a instância do WinHTTP na memória
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", "http://www.tomasvasquez.com.br/forum/feed.php"
 
    'Faz a requisição
    MyRequest.Send
 
    'obtém o conteúdo da resposta
    resposta = MyRequest.ResponseText
 
    stringTitulo = ""
    'procura pelo título do post, se houver
    retorno = InStr(1, resposta, stringTitulo, vbTextCompare)
 
    'itera até o próximo e até não encontrar mais
    While retorno > 0
        fimTitulo = InStr(retorno, resposta, "", vbTextCompare)
        respostaParcial = Left(resposta, fimTitulo)
        Debug.Print Right(respostaParcial, Len(respostaParcial) - retorno - Len(stringTitulo))
        resposta = Right(resposta, Len(resposta) - fimTitulo)
        retorno = InStr(retorno, resposta, stringTitulo, vbTextCompare)
    Wend
End Sub

The output is a bit dirty, but the code is clear enough to understand what is happening. Find below the result:

Macro Ultimas do Forum executada

Now it’s up to you. Enjoy!

Download the code file:

WinHTTP.zip (13.18 KiB)

References:

WinHTTP: https://msdn.microsoft.com/pt-br/library/windows/desktop/aa382925%28v=vs.85%29.aspx