הפעלת מאקרו בזמן

מקרה נפוץ מאוד בפועל: אתה צריך להפעיל אחד או יותר מפקודות המאקרו שלך בזמן נתון או בתדירות מסוימת. למשל, יש לכם דוח גדול וכבד שמתעדכן חצי שעה ואתם רוצים להריץ את העדכון חצי שעה לפני הגעתכם לעבודה בבוקר. או שיש לך מאקרו שאמור לשלוח אוטומטית מיילים לעובדים בתדירות מוגדרת. לחלופין, כשאתה עובד עם PivotTable, אתה רוצה שהוא יתעדכן כל 10 שניות, וכן הלאה.

בואו נסתכל על אילו יכולת של Excel ו-Windows ליישם זאת.

הפעלת מאקרו בתדירות נתונה

הדרך הקלה ביותר לעשות זאת היא להשתמש בשיטת VBA המובנית Application.OnTimeהמריץ את המאקרו שצוין בזמן שצוין. בואו נבין זאת בעזרת דוגמה מעשית.

פתח את עורך Visual Basic עם הכפתור באותו שם בכרטיסייה מפתחים (מפתח) או קיצור מקלדת אחר+F11, הכנס מודול חדש דרך התפריט הכנס - מודול והעתק לשם את הקוד הבא:

עמום TimeToRun 'משתנה גלובלי שבו מאוחסן זמן הריצה הבא 'זהו המאקרו הראשי Sub MyMacro() Application.Calculate 'חשב מחדש את הספר Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill תא A1 עם צבע אקראי :) קרא ל-NextRun 'הפעל את המאקרו NextRun כדי להגדיר את זמן הריצה הבא End Sub 'מאקרו זה מגדיר את הזמן להפעלה הבאה של המאקרו הראשי Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'הוסף 3 שניות לזמן הנוכחי Application.OnTime TimeToRun, "MyMacro" 'תזמן את הריצה הבאה End Sub 'מאקרו כדי להתחיל ברצף החוזר Sub Start() התקשר NextRun End Sub 'מאקרו כדי לעצור את רצף החוזר Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

בואו להבין מה יש כאן.

ראשית, אנחנו צריכים משתנה שיאחסן את הזמן של הריצה הבאה של המאקרו שלנו - קראתי לו זמן לרוץ. שים לב שהתוכן של משתנה זה חייב להיות זמין לכל פקודות המאקרו הבאות שלנו, ולכן עלינו לעשות זאת גלוֹבָּלִי, כלומר הכריז ממש בתחילת המודול לפני הראשון תַת.

הבא מגיע המאקרו הראשי שלנו MyMacro, שיבצע את המשימה העיקרית - לחשב מחדש את הספר בשיטה יישום. חשב. כדי להבהיר את זה, הוספתי לגיליון בתא A1 את הנוסחה =TDATE() שמציגה את התאריך והשעה – בחישוב מחדש התוכן שלה יתעדכן ממש לנגד עינינו (פשוט הפעל את תצוגת השניות בתא פוּרמָט). לכיף נוסף, הוספתי למאקרו גם את הפקודה למילוי תא A1 בצבע שנבחר באקראי (קוד הצבע הוא מספר שלם בטווח 0..56, שנוצר על ידי הפונקציה מקצה שיפורים ומעגל כלפי מעלה לפונקציה של מספר שלם Int).

מאקרו NextRun מוסיף לערך הקודם זמן לרוץ 3 שניות נוספות ואז מתזמן את ההרצה הבאה של המאקרו הראשי MyMacro לזמן החדש הזה. כמובן שבפועל, אתה יכול להשתמש בכל מרווחי זמן אחרים שאתה צריך על ידי הגדרת הארגומנטים של הפונקציה ערך זמן בפורמט hh:mm:ss.

ולבסוף, רק מטעמי נוחות, נוספו עוד פקודות מאקרו של השקת רצף. עמוד הבית והשלמתו סיום. האחרון משתמש בארגומנט השיטה הרביעית כדי לשבור את הרצף. בזמן שווה לא נכון.

סך הכל אם אתה מפעיל את המאקרו עמוד הבית, אז כל הקרוסלה הזו תסתובב, ונראה את התמונה הבאה על הגיליון:

אתה יכול לעצור את הרצף על ידי הפעלת המאקרו, בהתאמה סיום. מטעמי נוחות, ניתן להקצות קיצורי מקלדת לשתי פקודות המאקרו באמצעות הפקודה מאקרו - אפשרויות כרטיסייה מפתחים (מפתח - מאקרו - אפשרויות).

הפעלת מאקרו לפי לוח זמנים

כמובן, כל מה שתואר לעיל אפשרי רק אם יש לך Microsoft Excel פועל והקובץ שלנו פתוח בו. עכשיו נסתכל על מקרה מסובך יותר: צריך להפעיל את אקסל לפי לוח זמנים נתון, למשל, כל יום בשעה 5:00, לפתוח בו דו"ח גדול ומורכב ולעדכן בו את כל החיבורים והשאילתות כך שהוא תהיה מוכן עד שנגיע לעבודה 🙂

במצב כזה עדיף להשתמש מתזמן Windows - תוכנית המובנית במיוחד בכל גרסה של Windows שיכולה לבצע פעולות מוגדרות לפי לוח זמנים. למעשה, אתה כבר משתמש בו מבלי לדעת זאת, מכיוון שהמחשב שלך בודק בקביעות אם יש עדכונים, מוריד מסדי נתונים חדשים של אנטי-וירוס, מסנכרן תיקיות בענן וכו'. הכל העבודה של המתזמן. אז המשימה שלנו היא להוסיף למשימות הקיימות עוד אחת שתפעיל את אקסל ותפתח את הקובץ שצוין בו. ונתלה את המאקרו שלנו באירוע חוברת עבודה_פתוח הקובץ הזה - והבעיה נפתרה.

אני רוצה להזהיר אותך מיד שעבודה עם מתזמן עשויה לדרוש זכויות משתמש מתקדמות, כך שאם אינך יכול למצוא את הפקודות והפונקציות המתוארות להלן במחשב העבודה שלך במשרד, צור קשר עם מומחי ה-IT שלך לקבלת עזרה.

הפעלת מתזמן

אז בואו נתחיל את מתזמן. כדי לעשות זאת, אתה יכול:

• קליק ימני על הכפתור הַתחָלָה ולבחור ניהול מחשב (ניהול מחשב)
• בחר בלוח הבקרה: ניהול - מתזמן משימות (לוח בקרה - כלי ניהול - מתזמן משימות)
• בחר מהתפריט הראשי התחל – אביזרים – כלי מערכת – מתזמן משימות
• לחץ על קיצור מקשים Win+R, להיכנס taskschd.msc ולחץ זן

החלון הבא אמור להופיע על המסך (יש לי גרסה אנגלית, אבל אתה יכול גם לקבל גרסה):

צור משימה

כדי ליצור משימה חדשה באמצעות אשף פשוט שלב אחר שלב, לחץ על הקישור צור משימה פשוטה (צור משימה בסיסית) בחלונית הימנית.

בשלב הראשון של האשף, הזן את השם והתיאור של המשימה שתיווצר:

לחץ על הכפתור הַבָּא (הבא) ובשלב הבא נבחר טריגר - תדירות ההשקה או אירוע שיפעיל את המשימה שלנו (למשל, הפעלת המחשב):

אם בחרתם יומי (יומי), ואז בשלב הבא תצטרך לבחור זמן ספציפי, תאריך התחלה של הרצף והשלב (כל יום שני, יום חמישי וכו'):

השלב הבא הוא לבחור פעולה - הפעל את התוכנית (התחל תוכנית):

ולבסוף, הדבר המעניין ביותר הוא מה בדיוק צריך לפתוח:

ב תוכנית או תסריט (תוכנית/תסריט) עליך להזין את הנתיב ל-Microsoft Excel כתוכנית, כלומר ישירות לקובץ ההפעלה של Excel. במחשבים שונים עם גרסאות שונות של Windows ו-Office, קובץ זה עשוי להיות בתיקיות שונות, אז הנה כמה דרכים לגלות את מיקומו:

• לחץ לחיצה ימנית על הסמל (קיצור דרך) כדי להפעיל את Excel בשולחן העבודה או בשורת המשימות ובחר את הפקודה חומרים (נכסים), ולאחר מכן בחלון שנפתח, העתק את הנתיב מהשורה יעד:

                       

• פתח כל חוברת עבודה של Excel ולאחר מכן פתח מנהל המשימות (מנהל משימות) דוחף Ctrl+אחר+מ ובלחיצה ימנית על השורה Microsoft Excel, בחר פקודה חומרים (נכסים). בחלון שנפתח, אתה יכול להעתיק את הנתיב, לא שוכח להוסיף לו קו נטוי ו-EXCEL.EXE בסוף:

               

• פתח את Excel, פתח את עורך Visual Basic עם קיצור מקשים אחר+F11, פאנל פתוח מיידי שילוב של Ctrl+G, הזן את הפקודה לתוכו:

  ? יישום.נתיב

  … ולחץ על זן

  

  העתק את הנתיב שנוצר, לא שוכח להוסיף לו קו נטוי ו-EXCEL.EXE בסוף.

ב הוסף ארגומנטים (אופציונלי) (הוסף ארגומנטים (אופציונלי)) עליך להכניס את הנתיב המלא לספר עם המאקרו שברצוננו לפתוח.

כשהכל נכנס, לחץ הַבָּא ולאחר מכן סיום (סיים). יש להוסיף את המשימה לרשימה הכללית:

זה נוח לנהל את המשימה שנוצרה באמצעות הכפתורים בצד ימין. כאן תוכל לבדוק את המשימה על ידי הפעלתה מיד (לָרוּץ)מבלי להמתין לזמן שצוין. אתה יכול לבטל זמנית משימה (השבת)כך שהוא יפסיק לפעול לתקופה מסוימת, כמו החופשה שלך. ובכן, אתה תמיד יכול לשנות את הפרמטרים (תאריכים, שעה, שם קובץ) דרך הכפתור חומרים (נכסים).

הוסף מאקרו כדי לפתוח קובץ

כעת נותר לתלות בספר שלנו את השקת המאקרו שאנו צריכים באירוע פתיחת הקובץ. לשם כך, פתח את הספר ועבור לעורך Visual Basic באמצעות קיצור המקלדת אחר+F11 או כפתורים Visual Basic כרטיסייה מפתחים (מפתח). בחלון שנפתח בפינה השמאלית העליונה, אתה צריך למצוא את הקובץ שלנו על העץ וללחוץ פעמיים כדי לפתוח את המודול הספר הזה (חוברת עבודה זו).

אם אינך רואה חלון זה בעורך Visual Basic, תוכל לפתוח אותו דרך התפריט תצוגה - סייר פרויקטים.

בחלון המודול שנפתח, הוסף מטפל באירוע פתיחת ספר על ידי בחירתו מהרשימות הנפתחות בחלק העליון חוברת עבודה и להרחיב, בהתאמה:

תבנית נוהל צריכה להופיע על המסך. חוברת עבודה_פתוח, איפה בין השורות סאב פרטי и End Sub ואתה צריך להוסיף את פקודות VBA שאמורות להתבצע אוטומטית כאשר חוברת עבודה זו של Excel נפתחת, כאשר המתזמן פותח אותה בהתאם ללוח הזמנים. להלן כמה אפשרויות שימושיות לאוברקלוקינג:

• ספר העבודה הזה. RefreshAll - מרענן את כל שאילתות הנתונים החיצוניים, שאילתות Power Query וטבלאות PivotTables. האפשרות המגוונת ביותר. רק אל תשכח לאפשר חיבורים לנתונים חיצוניים כברירת מחדל ולעדכן קישורים באמצעות קובץ – אפשרויות – מרכז אמון – אפשרויות מרכז אמון – תוכן חיצוני, אחרת כשתפתחו את הספר תופיע אזהרה סטנדרטית ואקסל בלי לעדכן כלום ימתין לברכתכם בצורת לחיצה על הכפתור אפשר תוכן (אפשר תוכן):

  

• ActiveWorkbook.Connections(“Connection_Name”). רענן — עדכון נתונים בחיבור Connection_Name.
• Sheets ("Sheet5").PivotTables("PivotTable1«).PivotCache.רענון – עדכון טבלת ציר בודדת בשם PivotTable1 על הגיליון Sheet5.
• יישום. חשב - חישוב מחדש של כל חוברות העבודה הפתוחות של Excel.
• Application.CalculateFullRebuild – חישוב מחדש כפוי של כל הנוסחאות ובנייה מחדש של כל התלות בין התאים בכל חוברות העבודה הפתוחות (שווה ערך להזנה מחדש של כל הנוסחאות).
• דפי עבודה ("דוח"). PrintOut - גיליון הדפסה תמונות.
• התקשר ל-MyMacro – הפעל מאקרו בשם MyMacro.
• ספר העבודה הזה.שמור - שמור את הספר הנוכחי
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace(Now, ":", "-") & ".xlsx" - שמור את הספר בתיקייה D: ארכיון תחת השם תמונות עם תאריך ושעה מצורפים לשם.

אם אתה רוצה שהמאקרו יתבצע רק כאשר הקובץ נפתח על ידי המתזמן בשעה 5:00 בבוקר, ולא בכל פעם שהמשתמש פותח את חוברת העבודה במהלך יום העבודה, אז הגיוני להוסיף בדיקת זמן, למשל:

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

זה הכל. אל תשכח לשמור את חוברת העבודה שלך בפורמט המאפשר מאקרו (xlsm או xlsb) ותוכל לסגור את Excel בבטחה וללכת הביתה, להשאיר את המחשב דלוק. ברגע נתון (גם אם המחשב נעול), המתזמן יפעיל את Excel ויפתח בו את הקובץ שצוין, והמאקרו שלנו יבצע את הפעולות המתוכנתות. ותתרווח במיטה בזמן שהדוח הכבד שלך מחושב מחדש אוטומטית - יופי! 🙂

• מהן פקודות מאקרו, כיצד להשתמש בהן, היכן להכניס קוד Visual Basic באקסל
• כיצד ליצור תוספת מאקרו משלך עבור Excel
• כיצד להשתמש בחוברת העבודה המאקרו האישית כספרייה עבור פקודות המאקרו שלך ב- Excel