Πώς να ανοίξετε και να συμπληρώσετε το πρότυπο με το Excel VBA

Τα πρότυπα του Excel είναι συνήθως βιβλία εργασίας, με πλαίσιο αναφοράς, που συχνά υποστηρίζονται από συναρτήσεις. Ένα πρότυπο (xltx) μπορεί να χρησιμοποιηθεί ξανά και ξανά χωρίς να το μολύνει με δεδομένα. Μετά τον πληθυσμό με δεδομένα, ένα βιβλίο εργασίας προτύπου αποθηκεύεται ως xlsx, διατηρώντας την παρθένα κατάσταση του ίδιου του xltx.

Σε αυτήν την άσκηση θα χρησιμοποιήσουμε τον κώδικα VBA για να ανοίξουμε και να συμπληρώσουμε ένα πρότυπο. Το πρότυπο μπορεί να βρεθεί εδώ και το Excel Macro που χρησιμοποιείται μπορεί να βρεθεί εδώ.

Αυτό το άρθρο προϋποθέτει ότι ο αναγνώστης έχει εμφανιστεί την κορδέλα προγραμματιστή και είναι εξοικειωμένος με τον επεξεργαστή VBA. Εάν όχι, παρακαλώ "Google Tab Developer Excel" ή "Excel Code Window" της Google.

Το πρότυπο

Αρχικά, θα δημιουργήσουμε ένα εικονικό πρότυπο, συμπληρωμένο με δεδομένα, συγκεντρωτικό πίνακα και γράφημα, ως εξής:

Ανοίξτε ένα νέο αρχείο Excel. Μετονομασία "Sheet1" ως "Chart" και "Sheet2" as "Data"

Αντιγράψτε το ακόλουθο κείμενο, συμπεριλαμβανομένων των επικεφαλίδων, στο D1 της καρτέλας "Δεδομένα":

Επιλέξτε όλα τα δεδομένα, συμπεριλαμβανομένων των επικεφαλίδων στηλών, και εισαγάγετε έναν συγκεντρωτικό πίνακα στο Α1 του φύλλου "Δεδομένα", όπως φαίνεται παρακάτω.

Δημιουργήστε ένα γράφημα στην καρτέλα "Διάγραμμα", χρησιμοποιώντας τον συγκεντρωτικό πίνακα ως πηγή δεδομένων.

Αφαιρέστε τα δεδομένα στο D2: F15. Δεν είναι απαραίτητο να επαναφέρετε το εύρος δεδομένων του συγκεντρωτικού πίνακα. αφήστε το να συμπληρωθεί ακόμα και αν δεν υπάρχουν δεδομένα.  

Αποθηκεύστε το βιβλίο εργασίας ως "Κενό τεστ.xltx" σε έναν υποκατάλογο αυτού στον οποίο βρίσκεται το βιβλίο εργασίας μακροεντολών. Απαντήστε "Όχι" σε τυχόν ειδοποιήσεις από το Excel κατά τη διάρκεια της αποθήκευσης.

Χρειαζόμαστε επίσης έναν υποκατάλογο Αναφορών. Για παράδειγμα:

Αναφορές του Excel (xlsm αποθηκευμένο εδώ)

| _Templates (xlxt αποθηκευμένο εδώ)

       | _ Αναφορές (κάθε xlsx αποθηκεύεται εδώ)

Μόλις αποθηκευτεί ως xltx, κλείστε το πρότυπο

Η μακροεντολή

Ανοίξτε ένα νέο βιβλίο εργασίας για να κρατήσετε τον κωδικό μας. Μετονομάστε το "Φύλλο1" ως "Κύριο" και "Φύλλο2" ως "Βάση δεδομένων".

Τοποθετήστε ένα κουμπί στο "Main" για να οδηγήσετε την εφαρμογή.

Κανονικά, τα δεδομένα ανακτώνται από βάσεις δεδομένων. Δεδομένου ότι δεν έχουν όλοι στη βάση δεδομένων εύχρηστο, το φύλλο "Βάση δεδομένων" θα μιμηθεί έναν πίνακα βάσης δεδομένων.

Αντιγράψτε τα δεδομένα που βρίσκονται στην αρχή αυτού του άρθρου στην καρτέλα "Βάση δεδομένων" στο A1...

Ο Κώδικας

Η παρακάτω δομή κώδικα καθορίζει με σαφήνεια τις διαδικασίες:

• Λάβετε τα δεδομένα από τη «βάση δεδομένων».
• Ανοίξτε το πρότυπο.
• Συμπληρώστε το πρότυπο με τα δεδομένα και επαναφέρετε το εύρος δεδομένων του συγκεντρωτικού πίνακα.
• Αποθηκεύστε το πρότυπο ως αναφορά

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

Αντικείμενα δεδομένων ActiveX

Για να προσομοιώσουμε μια ανάγνωση βάσης δεδομένων, πρέπει να αναφερθούμε στη βιβλιοθήκη Active X. Αυτό γίνεται μέσω των Εργαλεία>Αναφορές από το παράθυρο κώδικα.

Δοκιμάστε τον Κώδικα

Εκχωρήστε το κουμπί στο "Κύριο" στο Υπο Openbook. Αποθηκεύστε το βιβλίο εργασίας ως "Πληθυσμός Templates.xlsm".

ΚΛΕΙΣΤΕ το βιβλίο εργασίας και ανοίξτε το ξανά.

Πατήστε το κουμπί για να δείτε το αποτέλεσμα. Αυξήστε τον αριθμό των σειρών δεδομένων στη "Βάση δεδομένων" και εκτελέστε ξανά, βλέποντας εάν το Διάγραμμα έχει ενημερωθεί με τις πρόσθετες πληροφορίες.

Στον παραπάνω κώδικα έχουμε δείξει το πρότυπο νωρίς, με XL. Ορατό = Αληθινό. Στο ζωντανό περιβάλλον, αυτό μπορεί να γίνει στο τέλος, έτσι ώστε οι ενημερώσεις οθόνης να μην είναι ορατές.

Αντιμετωπίστε την καταστροφή δεδομένων!

Λίγα πράγματα είναι πιο εκνευριστικά από το να καταρρεύσει ένα πολύ ανεπτυγμένο αρχείο Excel, καταστρέφοντας το αρχείο προέλευσης και χωρίς διαθέσιμο αντίγραφο ασφαλείας. Σε τέτοιες περιπτώσεις, όπου το Excel δεν καταφέρνει να ανακτήσει το κατεστραμμένο αρχείο, όλη η εργασία που έχει γίνει σε αυτό χάνεται, εκτός εάν έχετε ένα εργαλείο πρόχειρο για να... επιδιορθώστε το Excel αρχεία.

Είναι επίσης συνετό να δημιουργείτε συχνά αντίγραφα ασφαλείας πολύτιμων εργασιών.

Εισαγωγή συγγραφέα:

Ο Felix Hooker είναι ειδικός στην ανάκτηση δεδομένων DataNumen, Inc., η οποία είναι ο παγκόσμιος ηγέτης στις τεχνολογίες ανάκτησης δεδομένων, συμπεριλαμβανομένων επισκευή rar και sql προϊόντα λογισμικού ανάκτησης. Για περισσότερες πληροφορίες επισκεφθείτε www.datanumen.com