Come aprire e popolare il modello con Excel VBA

I modelli di Excel sono in genere cartelle di lavoro, con un framework di reporting, spesso supportato da funzioni. Un modello (xltx) può essere utilizzato più e più volte senza inquinarlo con i dati. Dopo il popolamento con i dati, una cartella di lavoro modello viene salvata come xlsx, preservando lo stato vergine dell'xltx stesso.

In questo esercizio utilizzeremo il codice VBA per aprire e popolare un modello. Il template possono essere trovati Qui. e la macro di Excel utilizzata può essere trovata Qui..

Questo articolo presuppone che il lettore abbia visualizzato la barra multifunzione dello sviluppatore e abbia familiarità con l'editor VBA. In caso contrario, Google "Excel Developer Tab" o "Excel Code Window".

Il template

Innanzitutto, creeremo un modello fittizio, popolato con dati, una tabella pivot e un grafico, come segue:

Apri un nuovo file Excel. Rinomina "Foglio1" come "Grafico" e "Foglio2" come "Dati"

Copia il seguente testo, incluse le intestazioni, in D1 della scheda "Dati":

Seleziona tutti i dati, comprese le intestazioni di colonna, e inserisci una tabella pivot in A1 del foglio "Dati", come mostrato di seguito.

Crea un grafico nella scheda "Grafico", utilizzando la tabella pivot come origine dati.

Rimuovere i dati in D2:F15. Non è necessario reimpostare l'intervallo di dati della tabella pivot; lascialo popolato anche se non ci sono dati.  

Salva la cartella di lavoro come "VacancyTemplate.xlx.” in una sottodirectory di quella in cui deve risiedere la macro cartella di lavoro. Rispondi "No" a tutti gli avvisi di Excel durante il salvataggio.

Avremo anche bisogno di una sottodirectory Reports. Per esempio:

Rapporti Excel (xlsm memorizzato qui)

|_Modelli (xlxt memorizzato qui)

       |_Rapporti (ogni xlsx salvato qui)

Una volta salvato come file xlx, chiudi il modello

La Macro

Apri una nuova cartella di lavoro per contenere il nostro codice. Rinomina "Foglio1" come "Principale" e "Foglio2" come "Database".

Posiziona un pulsante su "Principale" per guidare l'applicazione.

Normalmente, i dati vengono recuperati dai database. Poiché non tutti hanno un database a portata di mano, il foglio "Database" emulerà una tabella di database.

Copia i dati trovati alla start di questo articolo nella scheda "Database" in A1...

Il codice

La struttura del codice seguente definisce chiaramente i processi:

• Ottenere i dati dal "database";
• Apri il modello;
• Compilare il modello con i dati e reimpostare l'intervallo di dati della tabella pivot;
• Salva il modello come rapporto

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

Oggetti dati ActiveX

Per simulare una lettura del database, dobbiamo fare riferimento alla libreria Active Xrarsi. Fallo tramite Strumenti>Riferimenti dalla finestra del codice.

Prova il codice

Assegnare il pulsante su "Principale" a Sotto Openworkbook. Salvare la cartella di lavoro come "Populating Templates.xlsm".

CHIUDI la cartella di lavoro e riaprila.

Premere il pulsante per visualizzare il risultato. Aumenta il numero di righe di dati in "Database" ed esegui di nuovo, verificando se il grafico è stato aggiornato con le informazioni aggiuntive.

Nel codice sopra abbiamo mostrato il modello in anticipo, con XL.Visibile = Vero. Nell'ambiente live, questo può essere fatto alla fine, in modo che gli aggiornamenti dello schermo non siano visibili.

Affronta il disastro dei dati!

Poche cose sono più frustranti di un file Excel molto sviluppato che si arresta in modo anomalo, corrompendo il file sorgente e senza una copia di backup disponibile. In tali casi, in cui Excel non riesce a recuperare il file danneggiato, tutto il lavoro svolto su di esso è lost a meno che tu non abbia uno strumento a portata di mano correggere Excel File.

È inoltre prudente eseguire spesso il backup di lavori importanti.

Introduzione dell'autore:

Felix Hooker è un esperto di recupero dati in DataNumen, Inc., che è il leader mondiale nelle tecnologie di recupero dati, tra cui rar riparazione e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com