Como Importar XML para o Excel com um Botão Usando VBA

Aprenda como importar XML para o Excel com VBA usando um botão. Veja o código completo e o passo a passo para automatizar a tarefa.

Como Importar XML para o Excel com um Botão Usando VBA

Quer aprender como importar XML para o Excel de forma rápida, usando apenas um botão?
Neste tutorial, você vai ver o código VBA completo e entender passo a passo como configurar a macro para que o Excel faça todo o trabalho para você.

Para fazer o download do(s) arquivo(s) utilizados na aula, preencha com o seu e-mail:

Não vamos te encaminhar nenhum tipo de SPAM! A Hashtag Treinamentos é uma empresa preocupada com a proteção de seus dados e realiza o tratamento de acordo com a Lei Geral de Proteção de Dados (Lei n. 13.709/18). Qualquer dúvida, nos contate.

O que é XML

O XML (Extensible Markup Language) é uma linguagem de marcação usada para criar documentos com estrutura hierárquica.

É uma forma particular de organizar hierarquicamente itens, que mostramos em exemplo a seguir, para o arquivo de aula:

Importação de XML para o Excel

A ideia aqui é entender que existem itens mais internos, na hierarquia, e itens mais externos. Queremos, que cada item seja separado em colunas no Excel, ao serem importados.

Abaixo mostramos a tabela que vai receber os dados da importação XML VBA:

Como Importar XML para o Excel

Visão geral da importação

O objetivo é criar uma macro no Excel que, ao clicar em um botão, abra uma janela para selecionar um arquivo XML e, em seguida, importe automaticamente as informações para uma tabela.

Código VBA para importar XML

A seguir temos o código responsável por fazer essa importação XML Excel. Vamos acompanhar o código e seus comentários:

VBA
Sub importarXML()
'Importar a referência Microsoft XML, v6.0

Dim caminhoXML As String
Dim arqXML As New MSXML2.DOMDocument60
Dim CDs As MSXML2.IXMLDOMNode, CD As MSXML2.IXMLDOMNode
Dim linha As Long

With Application.FileDialog(msoFileDialogFilePicker)
    'Selecionar apenas um arquivo
    .AllowMultiSelect = False

    'Exibir apenas os arquivos de tipo .XML
    .Filters.Add "Arquivos XML", "*.xml", 1

    'Editar o título da caixa de seleção
    .Title = "Escolha um arquivo XML"    

    'Exibir a caixa de seleção
    .Show

    'Guardar o caminho do arquivo selecionado
    caminhoXML = .SelectedItems.Item(1)

End With

arqXML.Load (caminhoXML)

Set CDs = arqXML.SelectSingleNode("CATALOG")


linha = 2

For Each CD In CDs.ChildNodes
    Cells(linha, 1).Value = CD.SelectSingleNode("TITLE").Text
    Cells(linha, 2).Value = CD.SelectSingleNode("ARTIST").Text
    Cells(linha, 3).Value = CD.SelectSingleNode("COUNTRY").Text
    Cells(linha, 4).Value = CD.SelectSingleNode("COMPANY").Text
    Cells(linha, 5).Value = CD.SelectSingleNode("PRICE").Text
    Cells(linha, 6).Value = CD.SelectSingleNode("YEAR").Text
    linha = linha + 1
Next

Set arqXML = Nothing
Set CD = Nothing
Set CDs = Nothing

End Sub

Comentários a respeito do código para importar XML:

  • Fazemos o lembrete de ativar a referência XML de qualquer versão no seu VBA, como mostramos na videoaula
  • Dimensionar as variáveis:
    • “arqXML” como um novo objeto MSXML2.DOMDocument60
    • “CDs” como um objeto MSXML2.IXMLDOMNode
    • “CD” como um objeto MSXML2.IXMLDOMNode
    • “linha” como um objeto do tipo Long (número grande)
  • Vamos abrir a caixa de diálogo do Excel, com o comando/argumento para pegar/selecionar um arquivo (XML a ser importado)
    • Permitir apenas a seleção única (seleção múltipla configurada para ser falsa)
    • Permitir apenas arquivos com a extensão .XML
    • Configurar o título da caixa de diálogo como “Escolha um arquivo XML”
    • Mostrar a caixa de diálogo para o usuário escolher o arquivo a ser importado
    • Armazenar na variável “caminhoXML” o caminho completo do arquivo selecionado (item a ser selecionado na caixa de diálogo)
Ícone VBAVBA Impressionador

Como automatizar praticamente qualquer tarefa repetitiva que você faça dentro do Excel. Aprenda a criar integrações do Excel com o PowerPoint, Word, Outlook e muito mais. Aprenda a criar automações para resolver tudo com apenas um clique em um botão. Tudo isso com uma única linguagem que junta todos esses programas.

Black Friday · Lote Especial · Lote 1 · Último Lote

De R$ 1.497 56% OFF54% OFF53% OFF

por 12x R$ 6467,5069

Preço do Lote Especial por tempo limitadoPreço do Lote Especial só até amanhã, 13/11, às 23h59Preço do Lote Especial até 13/11, às 23h59Último dia do Lote Especial: o preço vale até hoje, às 23h59Preço do Lote 1 por tempo limitadoPreço do Lote 1 só até amanhã, 20/11, às 23h59Preço do Lote 1 até 20/11, às 23h59Último dia do Lote 1: o preço vale até hoje, às 23h59Preço do Último Lote por tempo limitadoPreço do Último Lote só até amanhã, 27/11, às 23h59Preço do Último Lote até 27/11, às 23h59Último dia do Último Lote: o preço vale até hoje, às 23h59

Começar agoraQuero a oferta de Black FridaySeta para a direita
Fundo VBATelas VBA

Execução e resultado

Após rodar o código e clicar no botão de Importar XML, a planilha é preenchida automaticamente com os dados estruturados, sem necessidade de copiar e colar manualmente

Observe abaixo a caixa de diálogo de exemplo gerada pelo código:

Como Importar XML para o Excel
  • Em seguida da escolha do arquivo, o arquivo será carregado para o VBA a partir do seu caminho de pasta, no computador
  • Configurar a variável “CDs” para receber o “nó” do arquivo XML representado pelo nó “CATALOG” (que contém todos os CDs e todos itens mais internos), uma variável complexa que abrange todos os objetos do XML
  • Armazenar na variável “linha” o valor 2
  • Utilizar de uma estrutura de repetição do tipo For Each, que vai varrer de CD em CD, que são os “nós” mais internos (ou filhos, child em inglês) do “CATALOG” (representado pela variável CDS), fazendo as ações:
    • Armazenar na célula de linha dada pela variável “linha”, e coluna A (1) o valor dado pelo texto retirado pelo nó “TITLE” mais interno, do XML
    • Armazenar na célula de linha dada pela variável “linha”, e coluna B (2) o valor dado pelo texto retirado pelo nó “ARTIST” mais interno, do XML
    • Armazenar na célula de linha dada pela variável “linha”, e coluna C (3) o valor dado pelo texto retirado pelo nó “COUNTRY” mais interno, do XML
    • Armazenar na célula de linha dada pela variável “linha”, e coluna D (4) o valor dado pelo texto retirado pelo nó “COMPANY” mais interno, do XML
    • Armazenar na célula de linha dada pela variável “linha”, e coluna E (5) o valor dado pelo texto retirado pelo nó “PRICE” mais interno, do XML
    • Armazenar na célula de linha dada pela variável “linha”, e coluna F (6) o valor dado pelo texto retirado pelo nó “YEAR” mais interno, do XML
    • Adicionar 1 unidade na variável “linha”, para podermos ir para a próxima linha de registro de informações
  • Esvaziar as variáveis “arqXML”, “CD” e “CDs” da memória do computador
  • Se encerra o código

Após rodar o código, apertando no botão de “Importar XML”, teremos rapidamente a tabela preenchida que queríamos, inicialmente. Veja:

Como Importar XML para o Excel

Conclusão - Como Importar XML para o Excel

Você aprendeu a importar XML para o Excel com VBA de forma simples, usando apenas um botão. Essa técnica é útil para automatizar tarefas repetitivas, economizar tempo e evitar erros de digitação.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: A aula explica, parte por parte, uma macro em VBA que abre uma caixa de diálogo para escolher o XML, carrega o arquivo e percorre cada CD de um catálogo de exemplo, gravando título, artista, país, empresa, preço e ano numa linha da planilha. O XML de nota fiscal fica de fora, por ter muito mais informação.

Neste vídeo (21 min):

  • 2:11 - O XML do exemplo aberto num editor de texto: um catálogo com vários CDs
  • 4:26 - Marcando a referência em Ferramentas, Referências
  • 4:44 - As variáveis: o caminho, o arquivo, a lista de CDs, um CD e a linha
  • 7:30 - O bloco With para configurar a caixa de diálogo
  • 10:01 - A caixa abre com título próprio e mostrando só arquivos XML
  • 11:31 - O arquivo escolhido é carregado e aparece na janela de variáveis locais
  • 13:59 - Selecionado o nó do catálogo, com 26 CDs dentro
  • 15:10 - Um For Each percorre cada CD do catálogo
  • 18:55 - Com F8, o primeiro CD vai para a linha 2 e a linha passa a valer 3
  • 19:20 - F5 roda o resto e preenche a planilha até o fim do XML
  • 20:00 - No fim, as variáveis de objeto recebem Nothing, como boa prática

Trechos do vídeo:

  • A leitura de XML no VBA depende da referência à biblioteca Microsoft XML; na gravação a mais atual era a 6.0, e uma versão anterior também serve, só com menos funcionalidades.
  • Escolher o arquivo por uma caixa de diálogo deixa a macro mais flexível do que um caminho escrito no código, que teria de ser editado toda vez que o arquivo mudasse.
  • Dentro de um bloco With, um ponto sem objeto antes se refere ao objeto do bloco, o que evita repetir o mesmo nome em cada linha.
  • XML é um formato de arquivo com tags, ou nós, umas dentro das outras; o nome das tags pode estar em qualquer idioma, desde que o código siga o padrão de nós daquele arquivo.
  • Para chegar à informação, o código desce de nó em nó e pega só o texto do nó final, e não o nó inteiro.

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!


Quer aprender mais sobre VBA com um minicurso básico gratuito?

Posts mais recentes de VBA

Posts mais recentes da Hashtag Treinamentos