Comment ouvrir et remplir un modèle avec Excel VBA

Les modèles Excel sont généralement des classeurs, avec un cadre de création de rapports, souvent pris en charge par des fonctions. Un modèle (xltx) peut être utilisé encore et encore sans le polluer avec des données. Après le remplissage avec des données, un classeur modèle est enregistré en tant que xlsx, préservant l'état vierge du xltx lui-même.

Dans cet exercice, nous utiliserons le code VBA pour ouvrir et remplir un modèle. Le gabarit peut être trouvé ici et la macro Excel utilisée peuvent être trouvées ici.

Cet article suppose que le lecteur a affiché le ruban Développeur et est familiarisé avec l'éditeur VBA. Si ce n'est pas le cas, veuillez Google "Excel Developer Tab" ou "Excel Code Window".

Le gabarit

Tout d'abord, nous allons créer un modèle factice, rempli de données, d'un tableau croisé dynamique et d'un graphique, comme suit :

Ouvrez un nouveau fichier Excel. Renommez « Sheet1 » en « Chart » et « Sheet2 » en « Data »

Copiez le texte suivant, y compris les titres, dans D1 de l'onglet "Données" :

Sélectionnez toutes les données, y compris les en-têtes de colonne, et insérez un tableau croisé dynamique en A1 de la feuille "Données", comme indiqué ci-dessous.

Créez un graphique dans l'onglet "Graphique", en utilisant le tableau croisé dynamique comme source de données.

Supprimez les données dans D2:F15. Il n'est pas nécessaire de réinitialiser la plage de données du tableau croisé dynamique ; laissez-le rempli même s'il n'y a pas de données.  

Enregistrez le classeur sous « VacancyTemplate.xlx.” dans un sous-répertoire de celui dans lequel le classeur de macros doit résider. Répondez "Non" à toutes les alertes d'Excel pendant la sauvegarde.

Nous aurons également besoin d'un sous-répertoire Reports. Par exemple:

Rapports Excel (xlsm stocké ici)

|_Gabarits (xlxt stocké ici)

       |_Rapports (chaque xlsx enregistré ici)

Une fois enregistré en tant que xlx, fermez le modèle

La macro

Ouvrez un nouveau classeur pour contenir notre code. Renommez "Sheet1" en "Main" et "Sheet2" en "Database".

Placez un bouton sur "Main" pour piloter l'application.

Normalement, les données sont extraites des bases de données. Étant donné que tout le monde n'a pas de base de données à portée de main, la feuille "Base de données" émulera une table de base de données.

Copiez les données figurant au début de cet article dans l’onglet « Base de données » à l’adresse A1…

Le code

La structure de code ci-dessous définit clairement les processus :

• Obtenir les données de la « base de données » ;
• Ouvrez le modèle ;
• Remplissez le modèle avec les données et réinitialisez la plage de données du tableau croisé dynamique ;
• Enregistrer le modèle en tant que rapport

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

Objets de données ActiveX

Pour simuler une lecture de base de données, il faut référencer la bibliothèque ActiveX. Pour ce faire, accédez à Outils > Références dans la fenêtre de code.

Testez le code

Attribuez le bouton sur "Principal" à Sous Openworkbook. Enregistrez le classeur sous le nom "Populating Templates.xlsm".

FERMEZ le classeur et rouvrez-le.

Appuyez sur le bouton pour afficher le résultat. Augmentez le nombre de lignes de données dans "Base de données" et exécutez à nouveau, en voyant si le graphique a été mis à jour avec les informations supplémentaires.

Dans le code ci-dessus, nous avons montré le modèle au début, avec XL.Visible = Vrai. Dans l'environnement en direct, cela peut être fait à la toute fin, de sorte que les mises à jour de l'écran ne soient pas visibles.

Faites face au désastre des données !

Rien n'est plus frustrant qu'un fichier Excel sur lequel on travaille depuis longtemps qui plante, corrompt le fichier source et qu'aucune copie de sauvegarde n'est disponible. Dans ce cas, si Excel ne parvient pas à récupérer le fichier endommagé, tout le travail effectué est perdu, à moins de disposer d'un outil de récupération sous la main. corriger Excel fichiers.

Il est également prudent de sauvegarder fréquemment un travail précieux.

Introduction de l'auteur:

Felix Hooker est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparation rar et produits logiciels de récupération sql. Pour plus d'informations, visitez www.datanumen.com