Χρήση αντικειμένου βιβλίου εργασίας στο Excel VBA (Άνοιγμα, Κλείσιμο, Αποθήκευση, Ορισμός)

Σε αυτό το σεμινάριο, θα καλύψω τον τρόπο εργασίας με βιβλία εργασίας στο Excel χρησιμοποιώντας VBA.

Στο Excel, ένα «Τετράδιο εργασίας» είναι ένα αντικείμενο που αποτελεί μέρος της συλλογής «Τετράδια εργασίας». Μέσα σε ένα βιβλίο εργασίας, έχετε διαφορετικά αντικείμενα, όπως φύλλα εργασίας, φύλλα γραφημάτων, κελιά και εύρη, αντικείμενα γραφήματος, σχήματα κ.λπ.

Με το VBA, μπορείτε να κάνετε πολλά πράγματα με ένα αντικείμενο βιβλίου εργασίας - όπως να ανοίξετε ένα συγκεκριμένο βιβλίο εργασίας, να αποθηκεύσετε και να κλείσετε βιβλία εργασίας, να δημιουργήσετε νέα βιβλία εργασίας, να αλλάξετε τις ιδιότητες του βιβλίου εργασίας κ.λπ.

Ας ξεκινήσουμε λοιπόν.

Όλοι οι κωδικοί που αναφέρω σε αυτό το σεμινάριο πρέπει να τοποθετηθούν στο Visual Basic Editor. Μεταβείτε στην ενότητα "Πού να βάλετε τον κώδικα VBA" για να μάθετε πώς λειτουργεί.

Εάν ενδιαφέρεστε να μάθετε VBA με τον εύκολο τρόπο, ελέγξτε το δικό μου Online Εκπαίδευση Excel VBA.

Αναφορά σε ένα βιβλίο εργασίας χρησιμοποιώντας VBA

Υπάρχουν διάφοροι τρόποι αναφοράς σε ένα αντικείμενο βιβλίου εργασίας στο VBA. Η μέθοδος που θα επιλέξετε εξαρτάται από το τι θέλετε να κάνετε. Σε αυτήν την ενότητα, θα καλύψω τους διαφορετικούς τρόπους παραπομπής σε ένα βιβλίο εργασίας μαζί με ορισμένους παραδείγματα κωδικών.

Χρήση ονομάτων βιβλίων εργασίας

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

Ας ξεκινήσουμε με ένα απλό παράδειγμα.

Εάν έχετε ανοίξει δύο βιβλία εργασίας και θέλετε να ενεργοποιήσετε το βιβλίο εργασίας με το όνομα - Examples.xlsx, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub ActivateWorkbook () Τετράδια εργασίας ("Examples.xlsx"). Ενεργοποιήστε το End Sub

Σημειώστε ότι πρέπει να χρησιμοποιήσετε το όνομα του αρχείου μαζί με την επέκταση εάν το αρχείο έχει αποθηκευτεί. Εάν δεν έχει αποθηκευτεί, τότε μπορείτε να χρησιμοποιήσετε το όνομα χωρίς επέκταση αρχείου.

Εάν δεν είστε σίγουροι τι όνομα θα χρησιμοποιήσετε, λάβετε βοήθεια από το Project Explorer.

Εάν θέλετε να ενεργοποιήσετε ένα βιβλίο εργασίας και να επιλέξετε ένα συγκεκριμένο κελί σε ένα φύλλο εργασίας σε αυτό το βιβλίο εργασίας, πρέπει να δώσετε ολόκληρη τη διεύθυνση του κελιού (συμπεριλαμβανομένου του βιβλίου εργασίας και του ονόματος του φύλλου εργασίας).

Sub ActivateWorkbook () Τετράδια εργασίας ("Examples.xlsx"). Φύλλα εργασίας ("Sheet1"). Ενεργοποίηση εύρους ("A1"). Επιλέξτε End Sub

Ο παραπάνω κώδικας ενεργοποιεί πρώτα το Sheet1 στο βιβλίο εργασίας Examples.xlsx και στη συνέχεια επιλέγει το κελί A1 στο φύλλο.

Συχνά θα βλέπετε έναν κωδικό όπου γίνεται αναφορά σε ένα φύλλο εργασίας ή σε ένα κελί/περιοχή χωρίς αναφορά στο βιβλίο εργασίας. Αυτό συμβαίνει όταν αναφέρεστε στο φύλλο εργασίας/περιοχές στο ίδιο βιβλίο εργασίας που περιέχει τον κώδικα και είναι επίσης το ενεργό βιβλίο εργασίας. Ωστόσο, σε ορισμένες περιπτώσεις, πρέπει να καθορίσετε το βιβλίο εργασίας για να βεβαιωθείτε ότι λειτουργεί ο κώδικας (περισσότερα για αυτό στην ενότητα ThisWorkbook).

Χρήση αριθμών ευρετηρίου

Μπορείτε επίσης να ανατρέξετε στα βιβλία εργασίας με βάση τον αριθμό ευρετηρίου τους.

Για παράδειγμα, εάν έχετε ανοίξει τρία βιβλία εργασίας, ο ακόλουθος κώδικας θα σας δείχνει τα ονόματα των τριών βιβλίων εργασίας σε ένα πλαίσιο μηνυμάτων (ένα κάθε φορά).

Sub WorkbookName () MsgBox Workbooks (1). Name MsgBox Workbooks (2). Name MsgBox Workbooks (3) .Name End Sub

Ο παραπάνω κώδικας χρησιμοποιεί MsgBox - η οποία είναι μια συνάρτηση που εμφανίζει ένα πλαίσιο μηνυμάτων με το καθορισμένο κείμενο/τιμή (που είναι το όνομα του βιβλίου εργασίας σε αυτήν την περίπτωση).

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

Το Excel αντιμετωπίζει το βιβλίο εργασίας που ανοίγει πρώτα για να έχει τον αριθμό ευρετηρίου ως 1 και το επόμενο ως 2 κ.ο.κ.

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

Ο παρακάτω κώδικας θα περιηγηθεί σε όλα τα ανοιχτά βιβλία εργασίας και θα κλείσει όλα εκτός από το βιβλίο εργασίας που έχει αυτόν τον κωδικό VBA.

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i). Ονόμασε αυτό το βιβλίο εργασίας. Ονόμασε τότε βιβλία εργασίας (i). Κλείσιμο Τέλος Αν Επόμενο i Τέλος Sub

Ο παραπάνω κώδικας μετρά τον αριθμό των ανοιχτών βιβλίων εργασίας και στη συνέχεια περνά από όλα τα βιβλία εργασίας χρησιμοποιώντας τον βρόχο Για κάθε.

Χρησιμοποιεί τη συνθήκη IF για να ελέγξει εάν το όνομα του βιβλίου εργασίας είναι το ίδιο με αυτό του βιβλίου εργασίας όπου εκτελείται ο κώδικας.

Εάν δεν είναι αντιστοιχία, κλείνει το βιβλίο εργασίας και μεταβαίνει στο επόμενο.

Σημειώστε ότι έχουμε τρέξει τον βρόχο από το WbCount στο 1 με ένα βήμα -1. Αυτό γίνεται καθώς με κάθε βρόχο, ο αριθμός των ανοιχτών βιβλίων εργασίας μειώνεται.

Αυτό το βιβλίο εργασίας καλύπτεται λεπτομερώς στην επόμενη ενότητα.

Χρησιμοποιώντας το ActiveWorkbook

Το ActiveWorkbook, όπως υποδηλώνει το όνομα, αναφέρεται στο βιβλίο εργασίας που είναι ενεργό.

Ο παρακάτω κώδικας θα σας δείξει το όνομα του ενεργού βιβλίου εργασίας.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name End Sub

Όταν χρησιμοποιείτε το VBA για να ενεργοποιήσετε ένα άλλο βιβλίο εργασίας, το τμήμα ActiveWorkbook στο VBA μετά από αυτό θα αρχίσει να αναφέρεται στο ενεργοποιημένο βιβλίο εργασίας.

Εδώ είναι ένα παράδειγμα αυτού.

Εάν έχετε ενεργό ένα βιβλίο εργασίας και εισάγετε τον ακόλουθο κώδικα σε αυτόν και τον εκτελείτε, θα εμφανίζεται πρώτα το όνομα του βιβλίου εργασίας που έχει τον κωδικό και στη συνέχεια το όνομα του Examples.xlsx (το οποίο ενεργοποιείται από τον κώδικα).

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Workbooks ("Examples.xlsx"). Ενεργοποιήστε το MsgBox ActiveWorkbook.Name End Sub

Σημειώστε ότι όταν δημιουργείτε ένα νέο βιβλίο εργασίας χρησιμοποιώντας το VBA, αυτό το πρόσφατα δημιουργημένο βιβλίο εργασίας γίνεται αυτόματα το ενεργό βιβλίο εργασίας.

Χρήση αυτού του βιβλίου εργασίας

Αυτό το βιβλίο εργασίας αναφέρεται στο βιβλίο εργασίας όπου εκτελείται ο κώδικας.

Κάθε βιβλίο εργασίας θα είχε ως αντικείμενο ένα αντικείμενο ThisWorkbook (ορατό στο Project Explorer).

Το "ThisWorkbook" μπορεί να αποθηκεύσει κανονικές μακροεντολές (παρόμοιες με αυτές που προσθέτουμε ενότητες) καθώς και διαδικασίες συμβάντων. Μια διαδικασία συμβάντος είναι κάτι που ενεργοποιείται με βάση ένα συμβάν - όπως το διπλό κλικ σε ένα κελί ή η αποθήκευση ενός βιβλίου εργασίας ή η ενεργοποίηση ενός φύλλου εργασίας.

Οποιαδήποτε διαδικασία συμβάντος που αποθηκεύετε σε αυτό το «ThisWorkbook» θα είναι διαθέσιμη σε ολόκληρο το βιβλίο εργασίας, σε σύγκριση με τα συμβάντα επιπέδου φύλλου που περιορίζονται μόνο στα συγκεκριμένα φύλλα.

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

Private Sub Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) MsgBox Target.Address End Sub

Ενώ ο κύριος ρόλος του ThisWorkbook είναι να αποθηκεύει τη διαδικασία συμβάντων, μπορείτε επίσης να το χρησιμοποιήσετε για να ανατρέξετε στο βιβλίο εργασίας όπου εκτελείται ο κώδικας.

Ο παρακάτω κώδικας θα επιστρέψει το όνομα του βιβλίου εργασίας στο οποίο εκτελείται ο κώδικας.

Sub ThisWorkbookName () MsgBox ThisWorkbook.Name End Sub

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

Δημιουργία νέου αντικειμένου βιβλίου εργασίας

Ο παρακάτω κώδικας θα δημιουργήσει ένα νέο βιβλίο εργασίας.

Sub CreateNewWorkbook () Workbooks.Add End Sub

Όταν προσθέτετε ένα νέο βιβλίο εργασίας, γίνεται το ενεργό βιβλίο εργασίας.

Ο ακόλουθος κώδικας θα προσθέσει ένα νέο βιβλίο εργασίας και στη συνέχεια θα σας δείξει το όνομα αυτού του βιβλίου εργασίας (το οποίο θα ήταν το προεπιλεγμένο όνομα τύπου Book1).

Sub CreateNewWorkbook () Workbooks.Add MsgBox ActiveWorkbook.Name End Sub

Ανοίξτε ένα βιβλίο εργασίας χρησιμοποιώντας το VBA

Μπορείτε να χρησιμοποιήσετε το VBA για να ανοίξετε ένα συγκεκριμένο βιβλίο εργασίας όταν γνωρίζετε τη διαδρομή αρχείου του βιβλίου εργασίας.

Ο παρακάτω κώδικας θα ανοίξει το βιβλίο εργασίας - Examples.xlsx που βρίσκεται στο φάκελο Έγγραφα στο σύστημά μου.

Sub OpenWorkbook () Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") End Sub

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

Sub OpenWorkbook () Workbooks.Open ("Examples.xlsx") End Sub

Σε περίπτωση που το βιβλίο εργασίας που προσπαθείτε να ανοίξετε δεν υπάρχει, θα δείτε ένα σφάλμα.

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

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

Sub OpenWorkbook () If Dir ("C: \ Users \ sumit \ Documents \ Examples.xlsx") "" Στη συνέχεια Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") Else MsgBox "Το αρχείο δεν Δεν υπάρχει "Τέλος Αν Τέλος Υπο

Μπορείτε επίσης να χρησιμοποιήσετε το παράθυρο διαλόγου Άνοιγμα για να επιλέξετε το αρχείο που θέλετε να ανοίξετε.

Sub OpenWorkbook () On Error Resume Next Dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub

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

Σε περίπτωση που ο χρήστης δεν ανοίξει ένα αρχείο και κάνει κλικ στο κουμπί Ακύρωση, το FilePath γίνεται False. Για να αποφύγουμε να λάβουμε σφάλμα σε αυτήν την περίπτωση, χρησιμοποιήσαμε τη δήλωση "On Error Resume Next".

Σχετίζεται με: Μάθετε τα πάντα για τον χειρισμό σφαλμάτων στο Excel VBA

Αποθήκευση βιβλίου εργασίας

Για να αποθηκεύσετε το ενεργό βιβλίο εργασίας, χρησιμοποιήστε τον παρακάτω κώδικα:

Sub SaveWorkbook () ActiveWorkbook.Save End Sub

Αυτός ο κώδικας λειτουργεί για τα βιβλία εργασίας που έχουν ήδη αποθηκευτεί νωρίτερα. Επίσης, δεδομένου ότι το βιβλίο εργασίας περιέχει την παραπάνω μακροεντολή, εάν δεν έχει αποθηκευτεί ως αρχείο .xlsm (ή .xls), θα χάσετε τη μακροεντολή όταν το ανοίξετε στη συνέχεια.

Εάν αποθηκεύετε το βιβλίο εργασίας για πρώτη φορά, θα σας εμφανιστεί μια προτροπή όπως φαίνεται παρακάτω:

Όταν αποθηκεύετε για πρώτη φορά, είναι καλύτερα να χρησιμοποιήσετε την επιλογή «Saveas».

Ο παρακάτω κώδικας θα αποθηκεύσει το ενεργό βιβλίο εργασίας ως αρχείο .xlsm στην προεπιλεγμένη τοποθεσία (που είναι ο φάκελος εγγράφων στο σύστημά μου).

Sub SaveWorkbook () ActiveWorkbook.SaveAs Όνομα αρχείου: = "Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

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

Sub SaveWorkbook () ActiveWorkbook.SaveAs Όνομα αρχείου: = "C: \ Users \ sumit \ Desktop \ Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Εάν θέλετε ο χρήστης να έχει την επιλογή να επιλέξει τη θέση αποθήκευσης του αρχείου, μπορείτε να χρησιμοποιήσετε την κλήση στο παράθυρο διαλόγου Saveas. Ο παρακάτω κώδικας εμφανίζει το παράθυρο διαλόγου Saveas και επιτρέπει στον χρήστη να επιλέξει τη θέση όπου θα πρέπει να αποθηκευτεί το αρχείο.

Sub SaveWorkbook () Dim FilePath As String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename: = FilePath & ".xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Σημειώστε ότι αντί να χρησιμοποιήσετε FileFormat: = xlOpenXMLWorkbookMacroEnabled, μπορείτε επίσης να χρησιμοποιήσετε το FileFormat: = 52, όπου 52 είναι ο κωδικός xlOpenXMLWorkbookMacroEnabled.

Αποθήκευση όλων των ανοιχτών βιβλίων εργασίας

Εάν έχετε περισσότερα από ένα βιβλία εργασίας ανοιχτά και θέλετε να αποθηκεύσετε όλα τα βιβλία εργασίας, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub SaveAllWorkbooks () Dim wb As Workbook For Every wb In Workbooks wb.Save Next wb End Sub

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

Εάν θέλετε να αποθηκεύσετε μόνο τα βιβλία εργασίας που έχουν αποθηκευτεί στο παρελθόν, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub SaveAllWorkbooks () Dim wb As Workbook For Every wb In Workbooks If wb.Path "" then wb.Save End If Next wb End Sub

Αποθήκευση και κλείσιμο όλων των βιβλίων εργασίας

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

Sub CloseandSaveWorkbooks () Dim wb As Workbook For Every wb In Workbooks If wb. Όνομα ThisWorkbook.Name Στη συνέχεια wb.Close SaveChanges: = True End If Next wb End Sub

Ο παραπάνω κώδικας θα έκλεινε όλα τα βιβλία εργασίας (εκτός από το βιβλίο εργασίας που έχει τον κωδικό - ThisWorkbook). Σε περίπτωση που υπάρχουν αλλαγές σε αυτά τα βιβλία εργασίας, οι αλλαγές θα αποθηκευτούν. Σε περίπτωση που υπάρχει ένα βιβλίο εργασίας που δεν έχει αποθηκευτεί ποτέ, θα εμφανίσει το παράθυρο διαλόγου αποθήκευση ως.

Αποθήκευση αντιγράφου του βιβλίου εργασίας (με χρονική σήμανση)

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

Εδώ είναι ο κώδικας VBA που θα δημιουργήσει ένα αντίγραφο του βιβλίου εργασίας σας και θα το αποθηκεύσει στην καθορισμένη τοποθεσία.

Sub CreateaCopyofWorkbook () ThisWorkbook.SaveCopyAs Όνομα αρχείου: = "C: \ Users \ sumit \ Desktop \ BackupCopy.xlsm" End Sub

Ο παραπάνω κώδικας θα αποθηκεύει ένα αντίγραφο του βιβλίου εργασίας σας κάθε φορά που εκτελείτε αυτήν τη μακροεντολή.

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

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

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.SaveCopyAs Filename: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-yy-hh-mm-ss -AMPM ") &" .xlsm "End Sub

Ο παραπάνω κώδικας δημιουργεί ένα αντίγραφο κάθε φορά που εκτελείτε αυτήν τη μακροεντολή και προσθέτει μια σφραγίδα ημερομηνίας/ώρας στο όνομα του βιβλίου εργασίας.

Δημιουργήστε ένα νέο βιβλίο εργασίας για κάθε φύλλο εργασίας

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

Αυτό μπορεί να συμβαίνει όταν έχετε μηνιαίες/τριμηνιαίες αναφορές σε ένα μόνο βιβλίο εργασίας και θέλετε να τις χωρίσετε σε ένα βιβλίο εργασίας για κάθε φύλλο εργασίας.

Or, εάν έχετε σοφές αναφορές στο τμήμα και θέλετε να τις χωρίσετε σε μεμονωμένα βιβλία εργασίας, ώστε να μπορείτε να στείλετε αυτά τα μεμονωμένα βιβλία εργασίας στους προϊσταμένους των τμημάτων.

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

Sub CreateWorkbookforWorksheets () Dim ws As Worksheet Dim wb As Workbook For Every ws In ThisWorkbook.Wheksheets Set wb = Workbooks.Add ws.Copy Before: = wb.Sheets (1) Application.DisplayAlerts = False wb.Sheets (2) .Delete Application.DisplayAlerts = Αληθινό wb.SaveAs "C: \ Users \ sumit \ Desktop \ Test \" & ws.Name & ".xlsx" wb.Κλείσιμο Επόμενο ws End Sub

Στον παραπάνω κώδικα, χρησιμοποιήσαμε δύο μεταβλητές "ws" και "wb".

Ο κώδικας περνά από κάθε φύλλο εργασίας (χρησιμοποιώντας τον βρόχο Για κάθε επόμενο) και δημιουργεί ένα βιβλίο εργασίας για αυτό. Χρησιμοποιεί επίσης τη μέθοδο αντιγραφής του αντικειμένου φύλλου εργασίας για να δημιουργήσει ένα αντίγραφο του φύλλου εργασίας στο νέο βιβλίο εργασίας.

Σημειώστε ότι έχω χρησιμοποιήσει τη δήλωση SET για να εκχωρήσω τη μεταβλητή "wb" σε οποιοδήποτε νέο βιβλίο εργασίας που δημιουργείται από τον κώδικα.

Μπορείτε να χρησιμοποιήσετε αυτήν την τεχνική για να αντιστοιχίσετε ένα αντικείμενο βιβλίου εργασίας σε μια μεταβλητή. Αυτό καλύπτεται στην επόμενη ενότητα.

Εκχώρηση αντικειμένου βιβλίου εργασίας σε μια μεταβλητή

Στο VBA, μπορείτε να αντιστοιχίσετε ένα αντικείμενο σε μια μεταβλητή και, στη συνέχεια, να χρησιμοποιήσετε τη μεταβλητή για να αναφερθείτε σε αυτό το αντικείμενο.

Για παράδειγμα, στον παρακάτω κώδικα, χρησιμοποιώ το VBA για να προσθέσω ένα νέο βιβλίο εργασίας και στη συνέχεια να εκχωρήσω αυτό το βιβλίο εργασίας στη μεταβλητή wb. Για να γίνει αυτό, πρέπει να χρησιμοποιήσω τη δήλωση SET.

Μόλις ορίσω το βιβλίο εργασίας στη μεταβλητή, όλες οι ιδιότητες του βιβλίου εργασίας διατίθενται και στη μεταβλητή.

Sub AssigntoVariable () Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filename: = "C: \ Users \ sumit \ Desktop \ Examples.xlsx" End Sub

Σημειώστε ότι το πρώτο βήμα στον κώδικα είναι να δηλώσετε το 'wb' ως μεταβλητή τύπου βιβλίου εργασίας. Αυτό λέει στη VBA ότι αυτή η μεταβλητή μπορεί να κρατήσει το αντικείμενο του βιβλίου εργασίας.

Η επόμενη πρόταση χρησιμοποιεί SET για να εκχωρήσει τη μεταβλητή στο νέο βιβλίο εργασίας που προσθέτουμε. Μόλις ολοκληρωθεί αυτή η ανάθεση, μπορούμε να χρησιμοποιήσουμε τη μεταβλητή wb για να αποθηκεύσουμε το βιβλίο εργασίας (ή να κάνουμε οτιδήποτε άλλο με αυτό).

Περιήγηση στα ανοιχτά βιβλία εργασίας

Έχουμε ήδη δει μερικά παραδείγματα κωδικών παραπάνω που χρησιμοποιούσαν looping στον κώδικα.

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

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

Sub CloseandSaveWorkbooks () Dim wb As Workbook For each wb In Workbooks If wb. Όνομα ThisWorkbook.Name Στη συνέχεια wb.Close SaveChanges: = True End If Next wb End Sub

Ο παραπάνω κώδικας χρησιμοποιεί τον βρόχο "Για κάθε" για να περάσει από κάθε βιβλίο εργασίας στη συλλογή βιβλίων εργασίας. Για να γίνει αυτό, πρέπει πρώτα να δηλώσουμε το 'wb' ως μεταβλητή τύπου βιβλίου εργασίας.

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

Το ίδιο μπορεί επίσης να επιτευχθεί με διαφορετικό βρόχο όπως φαίνεται παρακάτω:

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name Then Workbooks (i). Close SaveChanges: = True End If Next i End Sub

Ο παραπάνω κώδικας χρησιμοποιεί το βρόχο For Next για να κλείσει όλα τα βιβλία εργασίας εκτός από αυτό που έχει τον κώδικα. Σε αυτή την περίπτωση, δεν χρειάζεται να δηλώσουμε μια μεταβλητή βιβλίου εργασίας, αλλά αντίθετα, πρέπει να μετρήσουμε τον συνολικό αριθμό ανοιχτών βιβλίων εργασίας. Όταν έχουμε την καταμέτρηση, χρησιμοποιούμε τον βρόχο For Next για να περάσουμε από κάθε βιβλίο εργασίας. Επίσης, χρησιμοποιούμε τον αριθμό ευρετηρίου για να αναφέρουμε τα βιβλία εργασίας σε αυτήν την περίπτωση.

Σημειώστε ότι στον παραπάνω κώδικα, κάνουμε βρόχο από το WbCount στο 1 με το Βήμα -1. Αυτό είναι απαραίτητο καθώς με κάθε βρόχο, το βιβλίο εργασίας κλείνει και ο αριθμός των βιβλίων εργασίας μειώνεται κατά 1.

Σφάλμα κατά την εργασία με το αντικείμενο του βιβλίου εργασίας (Σφάλμα χρόνου εκτέλεσης ‘9’)

Ένα από τα πιο συνηθισμένα σφάλματα που ενδέχεται να αντιμετωπίσετε κατά την εργασία με βιβλία εργασίας είναι - Σφάλμα χρόνου εκτέλεσης ‘9’ - Υπόγνωση εκτός εμβέλειας.

Γενικά, τα σφάλματα VBA δεν είναι πολύ ενημερωτικά και συχνά σας αφήνουν να καταλάβετε τι πήγε στραβά.

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

• Το βιβλίο εργασίας στο οποίο προσπαθείτε να αποκτήσετε πρόσβαση δεν υπάρχει. Για παράδειγμα, εάν προσπαθώ να αποκτήσω πρόσβαση στο πέμπτο βιβλίο εργασίας χρησιμοποιώντας βιβλία εργασίας (5) και υπάρχουν μόνο 4 βιβλία εργασίας ανοιχτά, τότε θα λάβω αυτό το σφάλμα.
• Εάν χρησιμοποιείτε λάθος όνομα για να ανατρέξετε στο βιβλίο εργασίας. Για παράδειγμα, εάν το όνομα του βιβλίου εργασίας σας είναι Examples.xlsx και χρησιμοποιείτε το example.xlsx. τότε θα σας δείξει αυτό το σφάλμα.
• Εάν δεν έχετε αποθηκεύσει ένα βιβλίο εργασίας και χρησιμοποιείτε την επέκταση, τότε λαμβάνετε αυτό το σφάλμα. Για παράδειγμα, εάν το όνομα του βιβλίου εργασίας σας είναι Book1 και χρησιμοποιείτε το όνομα Book1.xlsx χωρίς να το αποθηκεύσετε, θα εμφανιστεί αυτό το σφάλμα.
• Το βιβλίο εργασίας στο οποίο προσπαθείτε να αποκτήσετε πρόσβαση είναι κλειστό.

Λάβετε μια λίστα με όλα τα ανοιχτά βιβλία εργασίας

Εάν θέλετε να λάβετε μια λίστα με όλα τα ανοιχτά βιβλία εργασίας στο τρέχον βιβλίο εργασίας (το βιβλίο εργασίας όπου εκτελείτε τον κώδικα), μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Τιμή = Τετράδια εργασίας (i). Όνομα Επόμενο i Τέλος υπο

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

Εάν θέλετε επίσης να λάβετε τη διαδρομή αρχείου τους, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Τιμή = Τετράδια εργασίας (i). Διαδρομή & "\" & Τετράδια εργασίας (i). Όνομα Επόμενο i Τέλος υπο

Ανοίξτε το καθορισμένο βιβλίο εργασίας κάνοντας διπλό κλικ στο κελί

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

Private Sub Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Workbooks. Open Target. Value End Sub

Αυτός ο κωδικός θα τοποθετηθεί στο παράθυρο κωδικού ThisWorkbook.

Για να το κάνω αυτό:

• Κάντε διπλό κλικ στο αντικείμενο ThisWorkbook στην εξερεύνηση έργου. Σημειώστε ότι το αντικείμενο ThisWorkbook πρέπει να βρίσκεται στο βιβλίο εργασίας όπου θέλετε αυτήν τη λειτουργία.
• Αντιγράψτε και επικολλήστε τον παραπάνω κώδικα.

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

Πού να τοποθετήσετε τον κωδικό VBA

Αναρωτιέστε πού πηγαίνει ο κώδικας VBA στο βιβλίο εργασίας του Excel;

Το Excel έχει ένα backend VBA που ονομάζεται VBA editor. Πρέπει να αντιγράψετε και να επικολλήσετε τον κώδικα στο παράθυρο κωδικού της λειτουργικής μονάδας VB Editor.

Ακολουθούν τα βήματα για να το κάνετε αυτό:

1. Μεταβείτε στην καρτέλα Προγραμματιστής.
2. Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
3. Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα. Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
4. Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
5. Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.

Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα Excel VBA:

• Πώς να καταγράψετε μια μακροεντολή στο Excel.
• Δημιουργία συνάρτησης καθορισμένης από το χρήστη στο Excel.
• Πώς να δημιουργήσετε και να χρησιμοποιήσετε πρόσθετο στο Excel.
• Πώς να επαναλάβετε τις μακροεντολές τοποθετώντας τις στο Personal Macro Workbook.
• Λήψη της λίστας των ονομάτων αρχείων από ένα φάκελο στο Excel (με και χωρίς VBA).
• Πώς να χρησιμοποιήσετε τη λειτουργία Excel VBA InStr (με πρακτικά ΠΑΡΑΔΕΙΓΜΑΤΑ).
• Πώς να ταξινομήσετε δεδομένα στο Excel χρησιμοποιώντας το VBA (Οδηγός βήμα προς βήμα).