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.
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:
Now it’s up to you. Enjoy!
Download the code file:
WinHTTP.zip (13.18 KiB)