Excel é provavelmente uma das ferramentas mais utilizada neste mundo, logo, a demanda por integrações com planilhas extremamente complexas é um cenário recorrente. As APIs permitem uma facilidade de acesso a informações em sistemas, o que vem se tornando cada vez mais padrão no mercado, com isso em mente, algumas demandas de conexão com sistemas via API no Excel são necessárias e muito úteis, então resolvi compartilhar um pouco de como criei essa integração. Vamos aprender como consultar APIs Rest usando o VBA e converter o resultado em JSON para ser usado na planilha.

Esse artigo espera que você saiba conceitos básicos de Excel e VBA, bem como o que é uma API e como funciona. Nosso objetivo será consultar uma API pública de Pokemons e listar o resultado na aba resultados.

Criando uma planilha em branco

Primeiro vamos criar uma planilha em branco com macro habilitada, dentro dela criarei uma aba chamada resultados.

Planilha Excel

Criando a macro para consultar a API

Pelo atalho alt + f11 vamos abrir o editor de macros do Excel, e criar um módulo chamado listaPokemons.

Macro VBA

Importando a biblioteca VBA-JSON

Como a API que vamos consultar retorna um JSON como resposta, vamos precisar importar a biblioteca VBA JSON, ela cuidará de todo trabalho chato de traduzir o JSON e retornar como uma matriz e objeto. A instalação é bem simples, basta baixar a última versão aqui e no editor de macros ir em Arquivo > Importar Arquivo > JsonConverter.bas.

Importar VBA JSON

Habilitando o Microsoft Scripting Runtime

Precisamos também habilitar o Microsoft Scripting Runtime, para isso basta navegar em Ferramentas > Referências e procurar e habilitar na lista o Microsoft Scripting Runtime.

Excel API Rest Microsoft Scripting Runtime

Criando a macro VBA para consultar a API REST

Abaixo temos o código completo da nossa requisição, ele pode parecer assustador, mas não se preocupe, explicarei o que cada parte está fazendo:

Sub listPokemons()
Dim json As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet
Dim objHTTP As Object

'Selecionamos nossa planilha resultados
Set ws = Worksheets("resultados")

'Criamos nosso objeto de requisção e enviamos
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://pokeapi.co/api/v2/pokemon"
objHTTP.Open "GET", URL, False
objHTTP.Send
strResult = objHTTP.responseText
json = strResult

Set objetoJson = JsonConverter.ParseJson(json)

'Criamos as células de cabeçalho
ws.Cells(1, 1) = "nome"
ws.Cells(1, 2) = "link"

'Fazemos um loop na propriedade results da resposta da API
i = 2 'Começaremos o contador na linha 2
For Each pokemon In objetoJson("results")
    ws.Cells(i, 1) = pokemon("name")
    ws.Cells(i, 2) = pokemon("url")
    i = i + 1
Next

End Sub

Primeiro definimos todas as variáveis que vamos utilizar em nosso scripts, incluindo a importação da biblioteca VBA JSON que importamos previamente em nosso projeto.

Dim json As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
Dim objHTTP As Object

Em seguida selecionamos a planilha que queremos exibir os resultados da consulta da API, no nosso caso Worksheets("resultados") e em seguida criamos um objeto que nos permitirá fazer a requisição para a API https://pokeapi.co/api/v2/pokemon. Pegaremos a resposta e colocaremos ela na variável json, por enquanto ela nada mais é que um texto.

'Selecionamos nossa planilha resultados
Set ws = Worksheets("resultados")

'Criamos nosso objeto de requisção e enviamos
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://pokeapi.co/api/v2/pokemon"
objHTTP.Open "GET", URL, False
objHTTP.Send
strResult = objHTTP.responseText
json = strResult

Aqui que a mágica acontece, a função ParseJson da biblioteca VBA JSON converte o texto da nossa variável json para um objeto acessível em nosso script. Agora conseguimos acessar todas as propriedades programaticamente em nosso código.

Set objetoJson = JsonConverter.ParseJson(json)

Agora que já temos o resultado da nossa API acessível, criamos na primeira linha da planilha o cabeçalho contendo as colunas nome e link.

'Criamos as células de cabeçalho
ws.Cells(1, 1) = "nome"
ws.Cells(1, 2) = "link"

Agora antes de analisar o script precisamos entender o resultado da API. Se você abrir o link https://pokeapi.co/api/v2/pokemon em seu navegador você verá o seguinte resultado:

{
  "count": 964,
  "next": "https://pokeapi.co/api/v2/pokemon?offset=20&limit=20",
  "previous": null,
  "results": [
    {
      "name": "bulbasaur",
      "url": "https://pokeapi.co/api/v2/pokemon/1/"
    },
    {
      "name": "ivysaur",
      "url": "https://pokeapi.co/api/v2/pokemon/2/"
    },
    {
      "name": "venusaur",
      "url": "https://pokeapi.co/api/v2/pokemon/3/"
    },
    {
      "name": "charmander",
      "url": "https://pokeapi.co/api/v2/pokemon/4/"
    },
    {
      "name": "charmeleon",
      "url": "https://pokeapi.co/api/v2/pokemon/5/"
    },
    {
      "name": "charizard",
      "url": "https://pokeapi.co/api/v2/pokemon/6/"
    },
    {
      "name": "squirtle",
      "url": "https://pokeapi.co/api/v2/pokemon/7/"
    },
    {
      "name": "wartortle",
      "url": "https://pokeapi.co/api/v2/pokemon/8/"
    },
    {
      "name": "blastoise",
      "url": "https://pokeapi.co/api/v2/pokemon/9/"
    },
    {
      "name": "caterpie",
      "url": "https://pokeapi.co/api/v2/pokemon/10/"
    },
    {
      "name": "metapod",
      "url": "https://pokeapi.co/api/v2/pokemon/11/"
    },
    {
      "name": "butterfree",
      "url": "https://pokeapi.co/api/v2/pokemon/12/"
    },
    {
      "name": "weedle",
      "url": "https://pokeapi.co/api/v2/pokemon/13/"
    },
    {
      "name": "kakuna",
      "url": "https://pokeapi.co/api/v2/pokemon/14/"
    },
    {
      "name": "beedrill",
      "url": "https://pokeapi.co/api/v2/pokemon/15/"
    },
    {
      "name": "pidgey",
      "url": "https://pokeapi.co/api/v2/pokemon/16/"
    },
    {
      "name": "pidgeotto",
      "url": "https://pokeapi.co/api/v2/pokemon/17/"
    },
    {
      "name": "pidgeot",
      "url": "https://pokeapi.co/api/v2/pokemon/18/"
    },
    {
      "name": "rattata",
      "url": "https://pokeapi.co/api/v2/pokemon/19/"
    },
    {
      "name": "raticate",
      "url": "https://pokeapi.co/api/v2/pokemon/20/"
    }
  ]
}

Estamos interessados na propriedaderesults, uma matriz contendo uma lista de pokemons com seus respectivos nomes e links para mais detalhes. Acessaremos essa matriz em objetoJson("results") e faremos um loop para exibir cada resultado de pokemon em uma nova linha da nossa tabela.

'Fazemos um loop na propriedade results da resposta da API
i = 2 'Começaremos o contador na linha 2
For Each pokemon In objetoJson("results")
    ws.Cells(i, 1) = pokemon("name")
    ws.Cells(i, 2) = pokemon("url")
    i = i + 1
Next

Se tudo ocorrer conforme esperado, ao pressionar f5 para rodar a nossa macro, na sua planilha você deverá ver o seguinte resultado:

Excel API Rest

Conclusão

Esse foi um exemplo bem simples de consulta, com o objeto de HTTP é possível realizar todos os tipos de requisições, GET, POST, UPDATE, … O interessante é entender como a requisição é feita e como você consegue exibir o resultado, graças a biblioteca VBA JSON, que já reduz drasticamente o trabalho necessário. Agora você só precisa adaptar esse fluxo e script para a sua necessidade.

5 9 votos
Nota do Artigo
Subscribe
Notify of
guest

11 Comentários
newest
oldest most voted
Inline Feedbacks
View all comments
leo
leo
1 ano atrás

Pra quem cair aqui:

Substitui:
objetoJson = jsonObject
pokemon = item

caso erro, declare:
Dim strResult As String
Dim Url As String

Adriano
Adriano
2 anos atrás

tentei fazer o mesmo trocando o link para a cotaçao historica do dolar do banco central e nao funcionou (https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@dataInicial=%2703-01-2021%27&@dataFinalCotacao=%2703-31-2021%27&$top=100&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao)

Madson Braz
Madson Braz
3 anos atrás

Vc declara: Dim jsonObject As Object
Mas usa: Set objetoJson = JsonConverter.ParseJson(json)

é Isso mesmo?

Madson Braz
Madson Braz
Reply to  Madson Braz
3 anos atrás

Outra questão:

Estou com erro 91-A variável do objeto ou a variável do bloco ‘With’ não foi definida nessa parte:

For Each p In jsonObject(“frete”)
      ws.Cells(i, 1) = p(“codigo_rastreio”)
      ws.Cells(i, 2) = p(“previsao_entrega”)
      i = i + 1
Next

Poderia me ajudar.

Madson Braz
Madson Braz
Reply to  Madson Braz
3 anos atrás

Esse é o codigo completo: Public Function Pedidos(pedido As String) As String   On Error GoTo Err_       Dim json As String   Dim jsonObject As Object, item As Object   Dim i As Long   Dim ws As Worksheet   Dim objHTTP As Object   Dim app_key As String, app_secret As String   Dim p As Dictionary   ‘Selecionamos nossa planilha resultados       Set ws = Worksheets(“dados”)       ‘Criamos nosso objeto de requisção e enviamos   app_key = “[app_key]”   app_secret = “[app_secret]”            Set objHTTP = CreateObject(“WinHttp.WinHttpRequest.5.1”)   Url = “https://app.omie.com.br/api/v1/produtos/pedidos/?JSON={“”call””:””ConsultarPedido””,””app_key””:””” & app_key & “””,””app_secret””:””” & app_secret & “””,””param””:[{“”numero_pedido””:” & pedido & “}]}”       objHTTP.Open “GET”, Url, False… Read more »

leo
leo
Reply to  Madson Braz
1 ano atrás

jsonObject(“frete”) – o que está dentro do (“”) é o nome da chave que vem no json

Lucas
Lucas
3 anos atrás

Parabéns pelo artigo Henrique!
No caso da api precisar de autenticação, sabe me dizer como poderia passar usuário e senha na requisição?

Lucas
Lucas
Reply to  Henrique Marques Fernandes
3 anos atrás

Obrigado pela resposta Henrique!
Mas não deu certo.
Fiz exatamente como você disse, mas ele retorna um erro “unauthorized”.
E com o mesmo usuário e senha pelo navegador ou por outra aplicação dá certo.

Paulo Roberto
Paulo Roberto
Reply to  Lucas
3 anos atrás

talvez esteja enviando os dados de login pelo Hearders da requisição, e não por parâmetro convencionais.

pedro
pedro
Reply to  Lucas
2 anos atrás
objHTTP.setRequestHeader "Authorization", "Basic " + Base64Encode("login123" +  ":" + "pass123")


Private Function Base64Encode(sText)
Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"
oNode.nodeTypedValue = StringToBinary(sText)


Base64Encode = oNode.Text
Set oNode = Nothing
Set oXML = Nothing
End Function

Private Function StringToBinary(Text)
Const adTypeText = 2
Const adTypeBinary = 1

Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeText
BinaryStream.Charset = "us-ascii"
BinaryStream.Open
BinaryStream.WriteText Text

'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeBinary

'Ignore first two bytes - sign of
BinaryStream.Position = 0

StringToBinary = BinaryStream.Read

Set BinaryStream = Nothing
End Function
11
0
Would love your thoughts, please comment.x