Category Archives: Web

Opiniões sobre tendências e o que anda acontecendo com este mundo desconhecido que é a internet, se é que isso é possível!

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

Chrome – How to clean cache, cookies and local storage for a specific domain

And here is one more post from the special series: “How it could be so simple?”. Actually, this is the first one, but such situations are so common in my routine that I will start to write more about this.

What inspired me to write this article, despite the hint, is how I found the solution. I had to clean cache, cookies and local databases from a specific domain on Chrome, of course, keeping other domains untouched. After 1 minute looking at Chrome config page, I quit and went to the web for some research. After another minute, I’ve found incredible forum posts, even in stackexchange, from guys asking for extensions to do it. Wait a minute. Is that a kind of basic goal too hard to reach?

I’ve stopped, think and… voilá! It’s a quite simple fellows:

On the page you want to clean cache, click right mouse button on a “free area” and select the “View page info” option.

View page info

On the options’ screen, select the “Show cookies and site data”:

Show cookies and site data

And then you might see this:

Cookies set by this page

On this screen, you can access to all domain data, from cookies to websql storage. Easy!

And all this happened just because I had to erase a simple local sqlite on my ripple emulator. 🙂