Da organização ao Preenchimento Automático de Documentos Word com Dados do Excel Usando VBA

Introdução:

Este artigo explora um código VBA (Visual Basic for Applications) projetado para automatizar o preenchimento de documentos Word com dados provenientes de uma planilha Excel. A solução é simples e se refere a ideia de gerar relatórios, contratos ou outros documentos personalizados em lote, visando economizar tempo e reduzir erros.

A ideia é apresentar alguns pontos interessantes de como pensar na hora de construir uma solução e por fim, apresentar alguns trechos de código usados no processo de criação.

Cenário do artigo:

• Trata-se de uma solução para um ambiente onde se utiliza versões antigas do Excel ou ferramenta gratuita, como Calc do LibreOffice.
• As fórmulas e funções serão apresentadas como alternativa a construção de segmentação, embora não seja o escopo mostrar como isso pode ser feito, mostro as funcionalidades das funções e como elas podem contribuir para construir uma tabela de consulta bem dinâmica.

Como organizar seus dados:

A organização dos dados define a qualidade e eficiência da consulta e das analises que serão feitas. Particularmente gosto de dividir os dados em tabelas simples e em diferentes, mas o exemplo deste artigo segue um modelo onde os dados ficam concentrados em uma única aba.

Também não gosto de colorir as abas e uso nomes mais curtos e que definem o que cada aba armazena. A imagem acima mostra algumas abas de uma planilha compartilhada na rede e que é acessada de forma assíncrona por diferentes usuários. A cor ajuda a identificar quais abas cada usuário costuma usar. Essa planilha em particular é utilizada tanto como banco de dados como aplicação. Ela permite que o usuário realize lançamentos na aba "Lançamento" ou "Pedidos Abertos", armazena os dados na aba "Base" e nas demais é possivél obter diferentes tipos de relatórios e dashboard.

Mas a pergunta é: Como os dados são organizados?

Os dados são organizados em tabelas simples e possuem chaves e chaves estrangeiras, um comportamento semelhante ao que ocorre no MySQL. Quando as tabelas não são consultadas diretamente pelo usuário criou uma aba apenas para elas (cada coluna se torna uma chave estrangeira de alguma tabela). No universo onde se preocupar com compatibilidade a criatividade ajuda.

A imagem acima mostra o cabeçalho de duas tabelas, a primeira relaciona os "Empenhos" (instrumento que indica que existe um valor reservado para ser gasto) e a outra o "Processo\Empenhos\Pedido" (o processo é instrumento administrativo para conduzir as ações da administração publica, e o pedido é solicitação do pagamento).

A segunda tabela é uma redundância, pois ela é a relação entre as tabelas de empenho, processos e pedido, que não são mostrados na imagem. A importância de entender isso é na hora de criar a macro para inserir os dados. Mas então a vem a pergunta: A segunda tabela realmente é importânte?

Poderia existir apenas as tabelas, Processo, Empenho e Pedido, outrossim, a segunda tabela poderia existir e as outras não. Através da segunda tabela, com uso dos recursos da tabela dinâmica as outras três seriam facilmente formadas. Porém, os dados não são gerados ao mesmo tempo e mesmo se opta-se por organiza-los dentro de uma única tabela será necessário considerar o custo da consulta durante cada insersão, e pior, neste caso não seria aplicável, pois existe vários pedidos pra uma mesmo Empenho, e vários Empenhos por Processo (Esta ideia é representada na imagem abaixo. Um diagramde de Entidade Relacionamento).

Em suma, entender a relação entre as tabelas e o tempo em que os dados são gerados lhe permite planejar a melhor forma de organiza-los. Primeiro são armazenados os dados dos Processos, que servem como base para que os dados dos Empenhos sejam armazenados, e esses também validam a inserção dos dados relacionados aos pedidos. Isso garante a confiabilidade do processo e pode ser facilmente aplicado com o recurso de Validador de dados do Excel. Outro detalhe é que não precisamos criar uma tabela dinâmica referenciando cada tabela, mas apenas a tabela que reúne as três, que neste artigo é a segunda tabela mostrada na imagem, ou seja, a tabela "Processo\Empenhos\Pedido".

Como usar as Funções "Índice, Corresp, Desloc e Procv":

Criar tabelas dinâmicas fica muito mais fácil quando se entende os atributos presentes em cada tabela. Nos parágrafos anteriores foi falado justamente sobre a parte de organização dos dados que implica em entende-los. Isso começa com o "schema " da tabela, em outras palavras, a estrutura de cabeçalho da tabela que conterá os dados. E é ai que entra o uso de cada uma dessas funções.

Sobre o Procv:

As funções possuem parâmetros que são separados por ponto e vírgula ";". O Procv busca um valor e retorna um possivel dado, ele é bem simples: você referência uma célula que será usado para buscar um valor; referência a matriz (intervalo de linhas e colunas) onde o valor que você precisa está armazenado; a coluna (índice numérico) onde o possível dado se encontra; e a correspondência exata ou relativa do valor procurado. O importante na verdade é entender a limitação do Procv e o resultado mais provável de sua execução.

A limitação do Procv é que ele realiza a busca com base apenas na primeira (1) coluna da matriz selecionada e o seu resultado mais provável é um erro "#N/D", que indica que o valor não foi encontrado. Então, se adapte ao Procv, trate o erro "#N/D" com um SeErro e pronto.

Sobre o Corresp:

O Corresp é o pivô das fórmulas que podemos criar. Ela busca um valor e retorna a posição (exata ou relativa) onde o valor foi encontrado. Para usar o Corresp você só não precisa referenciar a coluna (não existe esse parâmetro nessa função), como foi feito no Procv. Outra diferença é que Corresp possui três formas de correspondência: 1; 0; -1. Enquanto o Procv duas: 1 ou 0. Porém isso se resume em: Correspondência exata "0" ou Relativa "1 ou -1". Por isso, vamos nos concentrar na correspondência exata, que tem o mesmo sentido nos dois.

A limitação do Corresp e também é uma diferença em relação ao Procv, é que ao invés de matriz, você precisa referenciar um vetor no segundo parâmetro, ou seja, ou uma sequência (intervalo) de linhas, ou uma sequência de colunas. Outra limitação nos casos de correspondência relativa é que você precisa garantir que os dados estão ordenados. 

Sobre o Índice:

Esta função retorna um dado em função de sua posição em uma matriz referênciada. Diferente do Procv e Corresp, seu primeiro parâmetro é a matriz, a segunda é a linha (índice numérico) e a última a coluna, sendo esta, opcional. É comum usar o Corresp para buscar um valor e o Índice para retornar o dado na posição onde o valor buscado foi encontrado.

Imagine o seguinte, você tem uma tabela como esta:

Você pode usar o Corresp "=CORRESP( Consulta!A1 ;A2:A9;0)" e ao inserir “300” na célula A1 da aba Consulta, você obterá como resultado o índice 2, que significa que o valor “300” foi encontrado na segunda linha da referência “A2:A9”. Este é o primeiro processo que o Procv faz implicitamente, sem que percebamos.

Se usarmos o Índice para retornar algum valor com base no resultado anterior, devemos considerar qual a ordem do vetor que usamos no Corresp. Neste caso, a ordem foi vertical, ou seja, o Corresp nos dará a linha onde o valor foi encontrado. Sendo assim, o Índice ficaria: =ÍNDICE(A2:C9; “resultado anterior” ; “coluna”). Como a tabela da imagem possui três colunas, podemos escolher o valor da “coluna” como 1 (Itens), 2 (Descrição) ou 3 (Valores).

Mas parece estranho definir “coluna” como 1, pois retornaria o valor que está sendo buscado, porém essa seria uma forma de validar a entrada de busca, antes de alimentar outra fórmula ou função. Neste caso, você teria a certeza que este item existe antes de iniciar outro processo que dependa deste valor em específico.

Enfim, “=ÍNDICE(A2:C9; CORRESP( Consulta!A1 ;A2:A9;0) ; 3)” nos trás o valor do item pesquisado, e essa fórmula é equivalente ao Procv “=PROCV( Consulta!A1 ;A2:C9; 3 ;0). E a pergunta é: Qual devo usar?

Eu gosto de usar o Índice com Corresp na maioria das vezes, pois posso definir uma matriz fixa, e só alterar a referência do vetor dentro do Corresp, ou seja, eu posso escolher usar “A2:A9”, “B2:B9” ou “C2:C9”, dentro do Corresp sem me preocupar com a referência da matriz usada no Índice. Isso é muito, muito útil, quando você aplica a gestão de nomes no Excel.

Sobre o Desloc:

Pense nesta função como aquela que rompe as "limitações" do Corresp e do Procv, quanto a busca de valores. Tanto o Procv como o Corresp só retornam o primeiro valor encontrado, ou seja, não existe um parâmetro que indique quantos resultados devem ser encontrados antes de retornar um valor específico.

O Desloc retorna uma matriz ou um vetor, e isso depende do contexto que você está aplicando. Se for dentro de um Procv, será matriz, se for dentro de um Corresp, será um vetor.

Fique atento e pense um pouco ao usar o Desloc com Corresp, pois o corresp "reinicia" o índice das colunas ou linhas após o Desloc, ou seja, depois que as referências foram deslocadas, o Corresp conta as linhas ou colunas a partir desse novo vetor deslocado. Isso significa que o valor encontrado pode estar na posição 3 do vetor deslocado, mas na posição 7 no vetor antes do deslocamento. Mas isso é fácil de resolver, vamos seguindo.

Preparando os dados para o Relatório:

Definimos diferentes tabelas para representar informações distintas, entendemos o relacionamento entre elas e conhecemos o significado de seus atributos, ou elementos do cabeçalho. Então, depois de identificar as funções e construir fórmulas que podem nos ajudar a consultar as informações persistidas nas tabelas, devemos construir uma estrutura que permita extrair e exportar essas informações para um arquivo Word.

Esta é uma estrutura simples, queremos buscar todos os itens categorizados como "AA" então iremos inserir no Word o código do item, seu valor nominal e por extenso. A sua pergunta pode ser: Que "Cd. Busca" é esse, da onde saiu essa categorização "AA", "BB" etc. ?

Pense que seria o código do pedido, você solicita a compra de alguns itens, isso gera um código e você usa ele para relacionar os itens de um pedido.

Mão no código:

Eu não recomendo o uso do "Gravador de Macro", na verdade incentivo o bom estudo de como a lógica da programação em VBA funciona. Neste artigo não irei me aprofundar, porém indicarei um passo a passo básico que faço. Quando você terceiriza a parte de criação, você perde potencial de aprendizado e inovação.

• Primeiro Defino as váriaveis;

  Dim wApp As Object 'Word.Application - Permitindo integir com o Word através do código
  Dim wDoc As Object 'Word.Document - Instancia um arquivo word!
  Dim ws As Worksheet 'Define uma variável como uma planilha

Essas são as variáveis importantes, as demais seriam váriavies que armazenam valores simples, como um contador dentro de While, por exemplo.

• Depois divido o código em módulos, que seriam partes do código que realizam ações diferentes.

1. Preparativo;
2. Coleta de Dados;
3. Interação com Word;
4. Inserção de dados no Word;
5. Salvamento e Fechamento do Word;
6. Tratamento de Exceções;

• Por fim faço a refatoração do Código caso necessário.

Poucos passos, mas isso é apenas para apresentar a ideia. A refatoração é quando melhoramos nosso código. Primeiro deixo ele funcional, depois aplico melhorias. Para se ter ideia boa parte do tempo costumava a usar "Plan1.range( "A" & i ).Value" dentro de um "Do While", todas as vezes que começava a pensar em como interar sobre os dados, pois só declaro o "i" como Integer ou Long (variáveis numéricas) e já colocava o código para funcionar.

Preparativo:

Nesta etapa iremos definir em qual folha do Excel, ou aba, os dados que iremos inserir no Word estão persistido. Este passo não seria incluido, mas por muito tempo eu atribuia valores diretamente as variáveis que criava, mas neste caso e das outras variaveis destacadas anteriormente, a atribuição de valores ocorre usando a palavra-chave "Set". Isso ocorre pois estamos atribuindo um objeto a variável e não um valor simples, como um númeo ou texto.

Set ws = ThisWorkbook.Sheets("NomedaPlanilha")

Coletar dados do Excel:

Nesta parte estamos navegando dentro da folha do Excel especificada anteriormente, célula por célula na coluna "A". E iremos atribuir as variáveis código, valores e extenso, os valores das colunas "A", "B" e "C" respectivamente, em função do valor de "i", que neste caso representa alinha da planilha.

codigoBusca = InputBox("Digite o código de busca:")

Do While ws.Cells(i, "A").Value <> ""
  If ws.Cells(i, "A").Value = codigoBusca Then
      ReDim Preserve codigos(k), valores(k), extensos(k)
      codigos(k) = ws.Cells(i, "A").Value
      valores(k) = ws.Cells(i, "B").Value
      extensos(k) = ws.Cells(i, "C").Value
      k = k + 1
  End If
i = i + 1
Loop

Usamos o "Do While" para percorrer as células com base em uma condição, e "IF" para determinar quando os valores serão armazenados. O "ReDim Preserve codigos(k), valores(k), extensos(k)" é uma forma de redimensionar um array, array seria um vetor, vetor seria uma variável que contém uma sequência de valores. O "ReDim" pode ser evitado se você determinar primeiro a quantidade de itens que foram solicitados para cada Pedido. Então neste código ficaria a responsabilidade apenas de inserir os dados. Ou seja, este código faz duas coisas diferente e pode não ser o mais adequado.

Abertura e manipulação do Word:

Neste código criamos uma instância para trabalhar com o Word, deixamos ele visível para que possamos ver o que esta sendo feito e atribuímos um arquivo Word aberto a variável wDoc, que é o modelo onde iremos inserir os dados que estão no Excel.

Set wApp = CreateObject("Word.Application")
wApp.Visible = True
Set wDoc = wApp.Documents.Open("C:\caminho\Arquivo.docx")

Inserção de dados no Word:

Esta parte é relativa e depende de como está construída o seu modelo no Word. Para cada item do pedido é inserido um novo paragrafo no Word, com base no segundo paragrafo. Temos uma posição fixa determinada pela constante "POSICAO".

POSICAO = 5
For j = 0 To k - 1
  Set ParagrafoBase = wDoc.Paragraphs(2).Range
    
  'Adiciona um novo paragrafo
  Set novoParagrafo = wDoc.Paragraphs.Add.Range
  'Insere os dados no novo parágrafo
  novoParagrafo.Text = Mid(ParagrafoBase.Text, 1, POSICAO - 1) & codigos(j) & Mid(ParagrafoBase.Text, POSICAO) & valores(j) & " (" & extensos(j) & ")"
  novoParagrafo.InsertParagraphAfter
Next j

Obviamente não seria a melhor forma de inserir as informações presentes no segundo paragrafo, seria mais convidativo atribuir a variaveis e depois realizar este processo.

Outra observação é sobre o segundo parágrafo, será que ela deve permanecer no arquivo? Caso negativo seria interessante ou pensar em uma rotina para retira-la, ou depois de atribuir os valores a variáveis, usar o "InsertBefore", para inserir o primeiro item no proprio paragrafo 2.

Salvar e Fechar o Word:

Enfim o finalmente, aqui salvamos o arquivo com o nome do codigo usado na busca dos itens e fechamos ele e a aplicação do Word

wDoc.SaveAs2 "C:\caminho\" & codigoBusca & ".docx"
wDoc.Close
wApp.Quit

Pense em usar a instrução "With" para iniciar uma instancia com o Word.Application, e uma forma de verificar se o modelo do Word que você vai interagir está em uso.

Tratamento de Exceções:

Esta é uma parte bem simples, e na verdade deveria ser muito mais doque só o que estou apresentando, o tratamento de exceções vai muito, muito além do que só o código baixo. Aqui só estamos considerando que o código de busca, ou seja, o pedido que está sendo feito, não tem itens cadastrado.

If k > 0 Then
  ' ... (código para preencher o Word)
Else
  MsgBox "Nenhum dado encontrado para o código de busca: " & codigoBusca
End If

Consideração final:

Com um pouco de pesquisa e estudo, este artigo pode ajudar a iniciar um projeto bem legal. Lembre-se de realizar a limpeza das variáveis, como:

  Set wDoc = Nothing
  Set wApp = Nothing
  Set ws = Nothing

Isso para liberar a memória ocupada pelos objetos. Espero que seja útil, embora não tenha detalhes mais específicos e instruções mais detalhadas.

Grato pela sua atenção!!!