Como abrir e preencher o modelo com o Excel VBA

Os modelos do Excel geralmente são pastas de trabalho, com uma estrutura de relatórios, geralmente suportadas por funções. Um modelo (xltx) pode ser usado repetidamente sem poluir com dados. Após o preenchimento com dados, uma pasta de trabalho modelo é salva como um xlsx, preservando o estado virgem do próprio xltx.

Neste exercício, usaremos o código VBA para abrir e preencher um modelo. O modelo pode ser encontrado aqui e Excel Macro usado pode ser encontrado aqui.

Este artigo pressupõe que o leitor tenha a faixa Desenvolvedor exibida e esteja familiarizado com o Editor VBA. Se não, por favor, Google “Excel Developer Tab” ou “Excel Code Window”.

O modelo

Primeiro, vamos construir um modelo fictício, preenchido com dados, uma tabela dinâmica e um gráfico, como segue:

Abra um novo arquivo do Excel. Renomeie “Sheet1” como “Chart” e “Sheet2” como “Data”

Copie o seguinte texto, incluindo títulos, em D1 da guia “Dados”:

Selecione todos os dados, incluindo os cabeçalhos das colunas, e insira uma tabela dinâmica em A1 da planilha “Dados”, conforme mostrado abaixo.

Crie um gráfico na guia “Gráfico”, usando a tabela dinâmica como fonte de dados.

Remova os dados em D2:F15. Não é necessário redefinir o intervalo de dados da tabela dinâmica; deixe-o preenchido mesmo que não haja dados.  

Salve a pasta de trabalho como “VacancyTemplate.xltx.” em um subdiretório daquele em que a pasta de trabalho da macro deve residir. Responda "Não" a qualquer alerta do Excel durante o salvamento.

Também precisaremos de um subdiretório Reports. Por exemplo:

Relatórios Excel (xlsm armazenado aqui)

|_Modelos (xlxt armazenado aqui)

       |_Relatórios (cada xlsx salvo aqui)

Uma vez salvo como um xltx, feche o modelo

A macro

Abra uma nova pasta de trabalho para manter nosso código. Renomeie “Sheet1” como “Main” e “Sheet2” como “Database”.

Coloque um botão em “Principal” para conduzir o aplicativo.

Normalmente, os dados são recuperados de bancos de dados. Como nem todo mundo tem um banco de dados à mão, a folha “Banco de dados” irá emular uma tabela de banco de dados.

Copie os dados encontrados no start deste artigo na aba “Database” em A1…

O código

A estrutura de código abaixo define claramente os processos:

• Obtenha os dados do “banco de dados”;
• Abra o modelo;
• Preencha o modelo com os dados e redefina o intervalo de dados da tabela dinâmica;
• Salve o modelo como um relatório

Opção Explicit 'Cria objetos para representar a pasta de trabalho e planilhas modelo Public wb As Object Public XL As Object Public connDB As New ADODB.Connection Public rs As ADODB.Recordset Public eRow As Integer Public eRec As Integer Public dDate As String Sub openWorksheet() Call GetData Call OpenTemplate Call PopulateTemplate 'Salvar modelo como xlsx com carimbo de data No erro Resume Next dDate = Format(Now(), "yyyy.mm.dd") wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Sheets("Main").Activate 'Remover a guia do banco de dados wb.Activate 'Trazer o gráfico para o primeiro plano Set wb = Nothing Set XL = Nothing Set rs = Nothing Set connDB = Nothing End Sub Sub GetData() 'Emula a recuperação do banco de dados If connDB.State = 1 Then connDB.Close Sheets("Banco de Dados").Activate Sheets("Banco de Dados").Range("A1").Selecione Seleção. End(xlDown).Select eRec = ActiveCell.Row - 1 'estabelece quantos registros haverá no conjunto de registros 'Esta etapa não será necessária em um ambiente de banco de dados eRow = ActiveCell.Row 'a linha final, usada posteriormente no modelo connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ActiveWorkbook.FullName & ";" & _ "Propriedades estendidas=Excel 12.0;" Set rs = New ADODB.Recordset rs.Open "Select top " & eRec & " * from [Database$]", connDB, , , adCmdText End Sub Sub OpenTemplate() Set XL = CreateObject("Excel.Application") XL. Visible = True 'nos permite ver o que está acontecendo na depuração. XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx" Set wb = XL.ActiveWorkbook 'a nova pasta de trabalho é referenciada por "wb" End Sub Sub PopulateTemplate() wb.Sheets("Data").Activate wb .Sheets("Data").Range("D2").CopyFromRecordset rs wb.Sheets("Data").Range("A1").Selecione 'redimensionar o intervalo que direciona a tabela dinâmica, usando a variável eRow. wb.Sheets("Dados").PivotTables("Tabela Dinâmica1").ChangePivotCache wb. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _ Version:=xlPivotTableVersion14) wb.Sheets("Chart").Select End Sub

Objetos de dados ActiveX

Para simular uma leitura de banco de dados, devemos referenciar a biblioteca Active Xrary. Faça isso através de Ferramentas>Referências na janela de código.

Teste o código

Atribua o botão em “Principal” para Sub Openworkbook. Salve a pasta de trabalho como “Populated Templates.xlsm”.

FECHE a pasta de trabalho e abra-a novamente.

Pressione o botão para ver o resultado. Aumente o número de linhas de dados em “Database” e execute novamente, verificando se o Gráfico foi atualizado com as informações adicionais.

No código acima, mostramos o modelo inicial, com XL.Visível = Verdadeiro. No ambiente ao vivo, isso pode ser feito no final, para que as atualizações de tela não fiquem visíveis.

Lide com o desastre de dados!

Poucas coisas são mais frustrantes do que um arquivo do Excel muito desenvolvido travando, corrompendo o arquivo de origem e sem nenhuma cópia de backup disponível. Nesses casos, onde o Excel não consegue recuperar o arquivo danificado, todo o trabalho feito nele é lost a menos que você tenha uma ferramenta útil para corrigir Excel arquivos.

Também é prudente fazer backup com frequência de um trabalho valioso.

Introdução do autor:

Felix Hooker é um especialista em recuperação de dados em DataNumen, Inc., líder mundial em tecnologias de recuperação de dados, incluindo rar reparar e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com