Cómo abrir y completar una plantilla con Excel VBA

Las plantillas de Excel suelen ser libros de trabajo, con un marco de creación de informes, a menudo compatibles con funciones. Una plantilla (xltx) se puede utilizar una y otra vez sin contaminarla con datos. Después de la población con datos, se guarda un libro de trabajo de plantilla como xlsx, preservando el estado virgen del xltx en sí.

En este ejercicio usaremos código VBA para abrir y llenar una plantilla. La plantilla puede ser encontrado aquí y la macro de Excel utilizada se puede encontrar aquí.

Este artículo asume que el lector muestra la cinta de desarrollador y está familiarizado con el editor de VBA. De lo contrario, busque en Google "Pestaña de desarrollador de Excel" o "Ventana de código de Excel".

La plantilla

Primero, crearemos una plantilla ficticia, poblada con datos, una tabla dinámica y un gráfico, de la siguiente manera:

Abra un nuevo archivo de Excel. Cambiar el nombre de "Hoja1" como "Gráfico" y "Hoja2" como "Datos"

Copie el siguiente texto, incluidos los títulos, en el D1 de la pestaña "Datos":

Seleccione todos los datos, incluidos los encabezados de las columnas, e inserte una tabla dinámica en A1 de la hoja "Datos", como se muestra a continuación.

Cree un gráfico en la pestaña "Gráfico", utilizando la tabla dinámica como fuente de datos.

Elimina los datos en D2: F15. No es necesario restablecer el rango de datos de la tabla dinámica; déjelo poblado incluso si no hay datos.  

Guarde el libro de trabajo como “VacancyTemplate.xltx. " en un subdirectorio de aquel en el que residirá el libro de macros. Responda "No" a las alertas de Excel durante el guardado.

También necesitaremos un subdirectorio de informes. Por ejemplo:

Informes de Excel (xlsm almacenado aquí)

| _Plantillas (xlxt almacenado aquí)

       | _Informes (cada xlsx guardado aquí)

Una vez guardado como xltx, cierra la plantilla

La macro

Abra un nuevo libro de trabajo para contener nuestro código. Cambie el nombre de “Hoja1” como “Principal” y “Hoja2” como “Base de datos”.

Coloque un botón en "Principal" para impulsar la aplicación.

Normalmente, los datos se recuperan de bases de datos. Como no todo el mundo tiene una base de datos a mano, la hoja "Base de datos" emulará una tabla de base de datos.

Copie los datos encontrados en el start de este artículo en la pestaña "Base de datos" en A1 ...

El código

La siguiente estructura de código define claramente los procesos:

• Obtenga los datos de la "base de datos";
• Abra la plantilla;
• Complete la plantilla con los datos y restablezca el rango de datos de la tabla dinámica;
• Guarde la plantilla como informe

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 datos ActiveX

Para simular la lectura de una base de datos, debemos hacer referencia a la biblioteca Active Xrary. Haga esto a través de Herramientas> Referencias desde la ventana de código.

Prueba el código

Asigne el botón en "Principal" a Sub libro abierto. Guarde el libro de trabajo como "Poblando Templates.xlsm".

CERRAR el libro de trabajo y volver a abrirlo.

Presione el botón para ver el resultado. Aumente el número de filas de datos en “Base de datos” y vuelva a ejecutar, viendo si el Gráfico se ha actualizado con la información adicional.

En el código anterior, hemos mostrado la plantilla al principio, con XL.Visible = Verdadero. En el entorno en vivo, esto se puede hacer al final, para que las actualizaciones de la pantalla no sean visibles.

¡Haga frente al desastre de datos!

Pocas cosas son más frustrantes que un archivo de Excel muy desarrollado que falla, corrompe el archivo de origen y no tiene una copia de respaldo disponible. En tales casos, donde Excel no puede recuperar el archivo dañado, todo el trabajo realizado al respecto es lost a menos que tenga una herramienta a mano para arreglar Excel archivos.

También es prudente respaldar con frecuencia el trabajo valioso.

Introducción del autor:

Felix Hooker es un experto en recuperación de datos en DataNumen, Inc., que es el líder mundial en tecnologías de recuperación de datos, incluyendo rar reparación y productos de software de recuperación de sql. Para más información visite www.datanumen.com