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

Option Explicit
    'Create objects to represent the template workbook and worksheets
    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
    
    'Save template as datestamped xlsx
    On Error Resume Next
    dDate = Format(Now(), "yyyy.mm.dd")
    wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Sheets("Main").Activate        'Move off the database tab
     wb.Activate            'Bring the chart to the fore
     Set wb = Nothing
     Set XL = Nothing
     Set rs = Nothing
     Set connDB = Nothing
End Sub

Sub GetData()
    'Emulate database retrieval
    If connDB.State = 1 Then connDB.Close
    Sheets("Database").Activate
    Sheets("Database").Range("A1").Select
    Selection.End(xlDown).Select
    
    eRec = ActiveCell.Row - 1 'establish how many records will be in the recordset
                     'This step won't be needed in a database environment
    eRow = ActiveCell.Row   'the end row, used later in the template
    
    connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & ActiveWorkbook.FullName & ";" & _
      "Extended Properties=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       'enables us to see what's happening on debug.
    XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx"
    Set wb = XL.ActiveWorkbook             'the new workbook is referenced by "wb"
 End Sub
 
 Sub PopulateTemplate()
    wb.Sheets("Data").Activate
    wb.Sheets("Data").Range("D2").CopyFromRecordset rs
    wb.Sheets("Data").Range("A1").Select
    
    'resize the range driving the pivot table, using the eRow variable.
    wb.Sheets("Data").PivotTables("PivotTable1").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