Εκτέλεση μιας μακροεντολής έγκαιρα

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

Ας δούμε τι έχουν τη δυνατότητα να το υλοποιήσουν το Excel και τα Windows.

Εκτέλεση μιας μακροεντολής σε μια δεδομένη συχνότητα

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

Ανοίξτε το πρόγραμμα επεξεργασίας της Visual Basic με το ομώνυμο κουμπί στην καρτέλα προγραμματιστή (Προγραμματιστής) ή συντόμευση πληκτρολογίου άλλος+F11, εισάγετε μια νέα μονάδα μέσω του μενού Εισαγωγή – Ενότητα και αντιγράψτε τον παρακάτω κώδικα εκεί:

Dim TimeToRun 'καθολική μεταβλητή όπου αποθηκεύεται ο επόμενος χρόνος εκτέλεσης 'αυτή είναι η κύρια μακροεντολή Sub MyMacro() Application.Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill κελί A1 με τυχαίο χρώμα :) Καλέστε NextRun 'εκτελέστε τη μακροεντολή NextRun για να ορίσετε τον επόμενο χρόνο εκτέλεσης End Sub' αυτή η μακροεντολή ορίζει την ώρα για την επόμενη εκτέλεση της κύριας μακροεντολής Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'προσθέστε 3 δευτερόλεπτα στην τρέχουσα ώρα Εφαρμογή.OnTime TimeToRun, "MyMacro" 'προγραμματίστε την επόμενη εκτέλεση Τέλος Sub' macro για να ξεκινήσει η ακολουθία επανάληψης Sub Start() Καλέστε NextRun End Sub' macro για να σταματήσει η ακολουθία επανάληψης Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Ας καταλάβουμε τι είναι τι εδώ.

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

Ακολουθεί η κύρια μακροεντολή μας MyMacro, το οποίο θα εκτελέσει την κύρια εργασία – τον ​​επανυπολογισμό του βιβλίου χρησιμοποιώντας τη μέθοδο Εφαρμογή.Υπολογισμός. Για να γίνει πιο σαφές, πρόσθεσα τον τύπο =TDATE() στο φύλλο στο κελί A1, το οποίο εμφανίζει την ημερομηνία και την ώρα - όταν υπολογιστεί εκ νέου, το περιεχόμενό του θα ενημερωθεί ακριβώς μπροστά στα μάτια μας (απλώς ενεργοποιήστε την εμφάνιση δευτερολέπτων στο κελί μορφή). Για επιπλέον διασκέδαση, πρόσθεσα επίσης στη μακροεντολή την εντολή να γεμίσει το κελί A1 με ένα τυχαία επιλεγμένο χρώμα (ο κωδικός χρώματος είναι ένας ακέραιος αριθμός στην περιοχή 0..56, ο οποίος δημιουργείται από τη συνάρτηση γύρος και στρογγυλοποιεί σε μια ακέραια συνάρτηση Int).

Macro NextRun προσθέτει στην προηγούμενη τιμή TimeToRun 3 δευτερόλεπτα ακόμη και στη συνέχεια προγραμματίζει την επόμενη εκτέλεση της κύριας μακροεντολής MyMacro για αυτή τη νέα εποχή. Φυσικά, στην πράξη, μπορείτε να χρησιμοποιήσετε οποιαδήποτε άλλα χρονικά διαστήματα χρειάζεστε ορίζοντας τα ορίσματα συνάρτησης Χρονική αξία στη μορφή ωω:λλ:δδ.

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

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

Μπορείτε να σταματήσετε την ακολουθία εκτελώντας, αντίστοιχα, τη μακροεντολή φινίρισμα. Για ευκολία, μπορείτε να αντιστοιχίσετε συντομεύσεις πληκτρολογίου και στις δύο μακροεντολές χρησιμοποιώντας την εντολή Μακροεντολές – Επιλογές αυτί προγραμματιστή (Προγραμματιστής — Μακροεντολές — Επιλογές).

Εκτέλεση μακροεντολής σε χρονοδιάγραμμα

Φυσικά, όλα όσα περιγράφονται παραπάνω είναι δυνατά μόνο εάν έχετε το Microsoft Excel σε λειτουργία και το αρχείο μας είναι ανοιχτό σε αυτό. Τώρα ας δούμε μια πιο περίπλοκη περίπτωση: πρέπει να εκτελείτε το Excel σύμφωνα με ένα δεδομένο χρονοδιάγραμμα, για παράδειγμα, κάθε μέρα στις 5:00, να ανοίξετε μια μεγάλη και σύνθετη αναφορά σε αυτήν και να ενημερώσετε όλες τις συνδέσεις και τα ερωτήματα σε αυτήν, ώστε να να είστε έτοιμοι μέχρι να φτάσουμε στη δουλειά 🙂

Σε μια τέτοια κατάσταση, είναι καλύτερο να το χρησιμοποιήσετε Windows Scheduler – ένα πρόγραμμα ειδικά ενσωματωμένο σε οποιαδήποτε έκδοση των Windows που μπορεί να εκτελέσει συγκεκριμένες ενέργειες σε ένα χρονοδιάγραμμα. Στην πραγματικότητα, το χρησιμοποιείτε ήδη χωρίς να το γνωρίζετε, επειδή ο υπολογιστής σας ελέγχει τακτικά για ενημερώσεις, κατεβάζει νέες βάσεις δεδομένων προστασίας από ιούς, συγχρονίζει φακέλους cloud κ.λπ. Όλα είναι δουλειά του Προγραμματιστή. Έτσι, το καθήκον μας είναι να προσθέσουμε στις υπάρχουσες εργασίες μια άλλη που θα εκκινήσει το Excel και θα ανοίξει το καθορισμένο αρχείο σε αυτό. Και θα κρεμάσουμε τη μακροεντολή μας στην εκδήλωση Βιβλίο εργασίας_Άνοιγμα αυτό το αρχείο – και το πρόβλημα λύθηκε.

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

Εκκίνηση του Προγραμματιστή

Ας ξεκινήσουμε λοιπόν το Χρονοδιάγραμμα. Για να το κάνετε αυτό, μπορείτε είτε:

• Κάντε δεξί κλικ στο κουμπί Αρχική Και επιλέξτε Διαχείριση υπολογιστή (Διαχείριση υπολογιστή)
• Επιλέξτε στον Πίνακα Ελέγχου: Διαχείριση – Χρονοδιάγραμμα εργασιών (Πίνακας Ελέγχου — Εργαλεία διαχείρισης — Χρονοδιάγραμμα εργασιών)
• Επιλέξτε από το κύριο μενού Έναρξη – Αξεσουάρ – Εργαλεία συστήματος – Χρονοδιάγραμμα εργασιών
• Πατήστε συντόμευση πληκτρολογίου Νίκη+R, εισαγω taskschd.msc και πατήστε το πλήκτρο εισάγετε

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

Δημιουργήστε μια εργασία

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

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

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

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

Το επόμενο βήμα είναι να επιλέξετε μια ενέργεια - Εκτελέστε το πρόγραμμα (Ξεκινήστε ένα πρόγραμμα):

Και τέλος, το πιο ενδιαφέρον είναι τι ακριβώς πρέπει να ανοίξει:

Στο Πρόγραμμα ή σενάριο (Πρόγραμμα/σενάριο) πρέπει να εισαγάγετε τη διαδρομή προς το Microsoft Excel ως πρόγραμμα, δηλαδή απευθείας στο εκτελέσιμο Excel. Σε διαφορετικούς υπολογιστές με διαφορετικές εκδόσεις Windows και Office, αυτό το αρχείο μπορεί να βρίσκεται σε διαφορετικούς φακέλους, επομένως ακολουθούν μερικοί τρόποι για να μάθετε τη θέση του:

• Κάντε δεξί κλικ στο εικονίδιο (συντόμευση) για να εκκινήσετε το Excel στην επιφάνεια εργασίας ή στη γραμμή εργασιών και επιλέξτε την εντολή Υλικά (Ιδιότητες)και, στη συνέχεια, στο παράθυρο που ανοίγει, αντιγράψτε τη διαδρομή από τη γραμμή στόχος:

                       

• Ανοίξτε οποιοδήποτε βιβλίο εργασίας του Excel και, στη συνέχεια, ανοίξτε Task Manager (Διαχειριστής εργασιών) δραστήριος Ctrl+άλλος+Από και κάνοντας δεξί κλικ στη γραμμή Microsoft Excel, επιλέξτε μια εντολή Υλικά (Ιδιότητες). Στο παράθυρο που ανοίγει, μπορείτε να αντιγράψετε τη διαδρομή, μην ξεχνάτε να προσθέσετε μια ανάστροφη κάθετο σε αυτό και το EXCEL.EXE στο τέλος:

               

• Ανοίξτε το Excel, ανοίξτε το πρόγραμμα επεξεργασίας Visual Basic με συντόμευση πληκτρολογίου άλλος+F11, ανοιχτό πάνελ άμεσος ένας συνδυασμός Ctrl+G, εισάγετε την εντολή σε αυτό:

  ? Εφαρμογή.Διαδρομή

  … και κάντε κλικ στο εισάγετε

  

  Αντιγράψτε τη διαδρομή που προκύπτει, μην ξεχνάτε να προσθέσετε μια ανάστροφη κάθετο σε αυτό και το EXCEL.EXE στο τέλος.

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

Όταν εισαχθούν όλα, κάντε κλικ Επόμενο και στη συνέχεια φινίρισμα (Φινίρισμα). Η εργασία πρέπει να προστεθεί στη γενική λίστα:

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

Προσθέστε μια μακροεντολή για να ανοίξετε ένα αρχείο

Τώρα μένει να κρεμάσουμε στο βιβλίο μας την κυκλοφορία της μακροεντολής που χρειαζόμαστε στο συμβάν ανοιχτού αρχείου. Για να το κάνετε αυτό, ανοίξτε το βιβλίο και μεταβείτε στο πρόγραμμα επεξεργασίας της Visual Basic χρησιμοποιώντας τη συντόμευση πληκτρολογίου άλλος+F11 ή κουμπιά Visual Basic αυτί προγραμματιστή (Προγραμματιστής). Στο παράθυρο που ανοίγει στην επάνω αριστερή γωνία, πρέπει να βρείτε το αρχείο μας στο δέντρο και να κάνετε διπλό κλικ για να ανοίξετε τη μονάδα Αυτό το βιβλίο (Αυτό το Βιβλίο Εργασίας).

Εάν δεν βλέπετε αυτό το παράθυρο στο πρόγραμμα επεξεργασίας της Visual Basic, τότε μπορείτε να το ανοίξετε μέσω του μενού Προβολή — Project Explorer.

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

Ένα πρότυπο διαδικασίας θα πρέπει να εμφανιστεί στην οθόνη. Βιβλίο εργασίας_Άνοιγμα, όπου ανάμεσα στις γραμμές Ιδιωτικός υποπ и Sub End και πρέπει να εισαγάγετε εκείνες τις εντολές VBA που θα πρέπει να εκτελούνται αυτόματα όταν ανοίγει αυτό το βιβλίο εργασίας του Excel, όταν ο Χρονοπρογραμματιστής το ανοίγει σύμφωνα με το χρονοδιάγραμμα. Ακολουθούν ορισμένες χρήσιμες επιλογές για overclocking:

• This Workbook.RefreshAll – Ανανεώνει όλα τα ερωτήματα εξωτερικών δεδομένων, τα ερωτήματα Power Query και τους Συγκεντρωτικούς Πίνακες. Η πιο ευέλικτη επιλογή. Απλώς μην ξεχάσετε να επιτρέψετε τις συνδέσεις με εξωτερικά δεδομένα από προεπιλογή και να ενημερώσετε συνδέσμους μέσω Αρχείο – Επιλογές – Κέντρο εμπιστοσύνης – Επιλογές κέντρου εμπιστοσύνης – Εξωτερικό περιεχόμενο, διαφορετικά, όταν ανοίξετε το βιβλίο, θα εμφανιστεί μια τυπική προειδοποίηση και το Excel, χωρίς να ενημερώσει τίποτα, θα περιμένει την ευλογία σας με τη μορφή κλικ στο κουμπί Ενεργοποίηση περιεχομένου (Ενεργοποίηση περιεχομένου):

  

• ActiveWorkbook.Connections("Connection_Name").Ανανέωση — ενημέρωση δεδομένων στη σύνδεση Connection_Name.
• Φύλλα (“Φύλλο5").PivotTables("PivotTable1«).PivotCache.Ανανέωση – ενημέρωση ενός μόνο συγκεντρωτικού πίνακα με όνομα Συγκεντρωτικός πίνακας1 στο φύλλο Sheet5.
• Εφαρμογή.Υπολογισμός – επανυπολογισμός όλων των ανοιχτών βιβλίων εργασίας του Excel.
• Application.CalculateFullRebuild – αναγκαστικός επανυπολογισμός όλων των τύπων και επαναδημιουργία όλων των εξαρτήσεων μεταξύ των κελιών σε όλα τα ανοιχτά βιβλία εργασίας (ισοδύναμο με την εκ νέου εισαγωγή όλων των τύπων).
• Φύλλα εργασίας («Αναφορά»). Εκτύπωση – φύλλο εκτύπωσης Φωτογραφίες.
• Καλέστε το MyMacro – εκτελέστε μια μακροεντολή με όνομα MyMacro.
• This Workbook.Save – αποθηκεύστε το τρέχον βιβλίο
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace(Now, ":", "-") & ".xlsx" – αποθηκεύστε το βιβλίο σε φάκελο Δ: Αρχείο με το όνομα Φωτογραφίες με ημερομηνία και ώρα προσαρτημένη στο όνομα.

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

If Format(Now, "hh:mm") = "05:00" Τότε ThisWorkbook.RefreshAll  

Αυτό είναι όλο. Μην ξεχάσετε να αποθηκεύσετε το βιβλίο εργασίας σας σε μορφή με δυνατότητα μακροεντολής (xlsm ή xlsb) και μπορείτε να κλείσετε με ασφάλεια το Excel και να επιστρέψετε στο σπίτι, αφήνοντας τον υπολογιστή σας ενεργοποιημένο. Σε μια δεδομένη στιγμή (ακόμα κι αν ο υπολογιστής είναι κλειδωμένος), ο Χρονοδιάγραμμα θα εκκινήσει το Excel και θα ανοίξει το καθορισμένο αρχείο σε αυτό και η μακροεντολή μας θα εκτελέσει τις προγραμματισμένες ενέργειες. Και θα χλιδεύεις στο κρεβάτι ενώ η βαριά αναφορά σου υπολογίζεται αυτόματα ξανά – ομορφιά! 🙂

• Τι είναι οι μακροεντολές, πώς να τις χρησιμοποιήσετε, πού να εισαγάγετε τον κώδικα της Visual Basic στο Excel
• Πώς να δημιουργήσετε το δικό σας πρόσθετο μακροεντολής για το Excel
• Τρόπος χρήσης του βιβλίου εργασίας Personal Macro ως βιβλιοθήκη για τις μακροεντολές σας στο Excel