اجرای ماکرو به موقع

یک مورد بسیار رایج در عمل: شما باید یک یا چند ماکرو خود را در یک زمان معین یا در یک فرکانس مشخص اجرا کنید. به عنوان مثال، شما یک گزارش بزرگ و سنگین دارید که نیم ساعت به روز می شود و دوست دارید صبح نیم ساعت قبل از رسیدن به محل کار، آپدیت را اجرا کنید. یا یک ماکرو دارید که باید به طور خودکار ایمیل ها را در یک فرکانس مشخص برای کارمندان ارسال کند. یا، هنگام کار با PivotTable، می‌خواهید هر 10 ثانیه یکبار به‌روزرسانی شود و غیره.

بیایید ببینیم اکسل و ویندوز چه توانایی هایی برای پیاده سازی این کار دارند.

اجرای یک ماکرو در یک فرکانس مشخص

ساده ترین راه برای انجام این کار استفاده از روش VBA داخلی است Application.OnTimeبرنامه ای که ماکرو مشخص شده را در زمان مشخص اجرا می کند. بیایید این را با یک مثال عملی درک کنیم.

ویرایشگر ویژوال بیسیک را با دکمه ای به همین نام در تب باز کنید توسعه دهنده (توسعه دهنده) یا میانبر صفحه کلید دگرساز+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 ثانیه به زمان فعلی اضافه کنید Application.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 است که توسط تابع تولید می شود) به ماکرو اضافه کردم. RND و به یک تابع عدد صحیح گرد می شود INT).

درشت دستور اجرای بعدی به ارزش قبلی اضافه می کند TimeToRun 3 ثانیه دیگر و سپس اجرای بعدی ماکرو اصلی را برنامه ریزی می کند MyMacro برای این زمان جدید البته در عمل می توانید با تنظیم آرگومان های تابع از هر بازه زمانی دیگری که نیاز دارید استفاده کنید ارزش زمانی در قالب hh:mm:ss.

و در نهایت، فقط برای راحتی، ماکروهای راه اندازی توالی بیشتری اضافه شده است. صفحه اصلی و تکمیل آن پایان. آخرین مورد از آرگومان متد چهارم برای شکستن دنباله استفاده می کند. در زمان برابر غلط.

مجموع اگر ماکرو را اجرا کنید صفحه اصلی، سپس کل این چرخ فلک می چرخد ​​و تصویر زیر را در برگه می بینیم:

می توانید به ترتیب با اجرای ماکرو دنباله را متوقف کنید پایان. برای راحتی، می توانید با استفاده از دستور، میانبرهای صفحه کلید را به هر دو ماکرو اختصاص دهید ماکروها - گزینه ها برگ توسعه دهنده (توسعه‌دهنده - ماکروها - گزینه‌ها).

اجرای ماکرو بر اساس برنامه

البته همه مواردی که در بالا توضیح داده شد تنها در صورتی امکان پذیر است که مایکروسافت اکسل را در حال اجرا داشته باشید و فایل ما در آن باز باشد. حالا بیایید به یک مورد پیچیده تر نگاه کنیم: شما باید اکسل را طبق یک برنامه زمان بندی مشخص اجرا کنید، به عنوان مثال، هر روز ساعت 5:00، یک گزارش بزرگ و پیچیده را در آن باز کنید و تمام اتصالات و پرس و جوهای موجود در آن را به روز کنید. تا رسیدن به محل کار آماده باشید 🙂

در چنین شرایطی بهتر است استفاده شود Windows Scheduler – برنامه ای که به طور ویژه در هر نسخه ای از ویندوز تعبیه شده است که می تواند اقدامات مشخص شده را در یک برنامه زمان بندی انجام دهد. در واقع، شما در حال حاضر بدون اطلاع از آن استفاده می‌کنید، زیرا رایانه شما مرتباً به‌روزرسانی‌ها را بررسی می‌کند، پایگاه‌های داده ضد ویروس جدید را دانلود می‌کند، پوشه‌های ابری را همگام‌سازی می‌کند، و غیره. همه اینها کار Scheduler است. بنابراین وظیفه ما این است که یکی دیگر را به وظایف موجود اضافه کنیم که اکسل را راه اندازی کند و فایل مشخص شده را در آن باز کند. و ما کلان خود را در رویداد آویزان خواهیم کرد کتاب کار_باز کردن این فایل - و مشکل حل شده است.

می‌خواهم فوراً به شما هشدار دهم که کار با Scheduler ممکن است به حقوق کاربر پیشرفته نیاز داشته باشد، بنابراین اگر نمی‌توانید دستورات و عملکردهای شرح داده شده در زیر را در رایانه کاری خود در دفتر پیدا کنید، برای کمک با متخصصان فناوری اطلاعات خود تماس بگیرید.

راه اندازی زمانبند

پس بیایید زمانبندی را شروع کنیم. برای انجام این کار، می توانید یکی از موارد زیر را انجام دهید:

• روی دکمه کلیک راست کنید آغاز و انتخاب کنید مدیریت کامپیوتر (مدیریت کامپیوتر)
• در کنترل پنل انتخاب کنید: مدیریت - زمانبندی وظایف (کنترل پنل - ابزارهای اداری - زمانبندی کار)
• از منوی اصلی انتخاب کنید شروع – لوازم جانبی – ابزارهای سیستم – زمانبندی کار
• میانبر صفحه کلید را فشار دهید پیروزی+R، وارد taskchd.msc و مطبوعات وارد

پنجره زیر باید روی صفحه ظاهر شود (من یک نسخه انگلیسی دارم، اما شما می توانید یک نسخه نیز داشته باشید):

ایجاد یک کار

برای ایجاد یک کار جدید با استفاده از یک جادوگر گام به گام ساده، روی پیوند کلیک کنید یک کار ساده ایجاد کنید (ایجاد کار اصلی) در پانل سمت راست

در اولین مرحله از ویزارد، نام و شرح وظیفه ای که باید ایجاد شود را وارد کنید:

روی دکمه کلیک کنید بعدی (بعد) و در مرحله بعدی یک ماشه را انتخاب می کنیم - فرکانس راه اندازی یا رویدادی که وظیفه ما را راه اندازی می کند (به عنوان مثال، روشن کردن رایانه):

اگر انتخاب کردید روزانه (روزانه)، سپس در مرحله بعد باید یک زمان خاص، تاریخ شروع دنباله و مرحله (هر روز دوم، روز پنجم و غیره) را انتخاب کنید.

مرحله بعدی انتخاب یک عمل است - برنامه را اجرا کنید (شروع یک برنامه):

و در نهایت، جالب ترین چیز این است که دقیقاً چه چیزی باید باز شود:

در برنامه یا اسکریپت (برنامه/اسکریپت) شما باید مسیر مایکروسافت اکسل را به عنوان یک برنامه وارد کنید، یعنی مستقیماً به فایل اجرایی اکسل. در رایانه‌های مختلف با نسخه‌های مختلف ویندوز و آفیس، این فایل ممکن است در پوشه‌های متفاوتی باشد، بنابراین در اینجا چند راه برای پیدا کردن مکان آن وجود دارد:

• بر روی آیکون (میانبر) کلیک راست کنید تا اکسل روی دسکتاپ یا در نوار وظیفه راه اندازی شود و دستور را انتخاب کنید. مواد (خواص)و سپس در پنجره باز شده مسیر را از خط کپی کنید هدف:

                       

• هر کتاب کار اکسل را باز کنید، سپس باز کنید وظیفه مدیر (مدیر وظایف) هل دادن کلیدهای Ctrl+دگرساز+از جانب و با کلیک راست روی خط مایکروسافت اکسل، یک دستور را انتخاب کنید مواد (خواص). در پنجره باز شده می توانید مسیر را کپی کنید فراموش نکنید که یک بک اسلش به آن و در پایان EXCEL.EXE اضافه کنید:

               

• اکسل را باز کنید، ویرایشگر ویژوال بیسیک را با میانبر صفحه کلید باز کنید دگرساز+F11، پنل را باز کنید فوری ترکیبی از کلیدهای Ctrl+G، دستور را در آن وارد کنید:

  ? برنامه. مسیر

  … و کلیک کنید وارد

  

  مسیر حاصل را کپی کنید، فراموش نکنید که یک بک اسلش به آن و در پایان EXCEL.EXE اضافه کنید.

در اضافه کردن استدلال (اختیاری) (افزودن آرگومان ها (اختیاری)) شما باید مسیر کامل کتاب را با ماکرویی که می خواهیم باز کنیم وارد کنید.

وقتی همه چیز وارد شد، سپس کلیک کنید بعدی و پس از آن پایان (پایان). وظیفه باید به لیست کلی اضافه شود:

مدیریت کار ایجاد شده با استفاده از دکمه های سمت راست راحت است. در اینجا می توانید کار را با اجرای فوری آن آزمایش کنید (اجرا کن)بدون انتظار برای زمان مشخص شده می توانید به طور موقت یک کار را غیرفعال کنید (غیر فعال کردن)به طوری که برای مدتی مانند تعطیلات شما کار را متوقف می کند. خوب، شما همیشه می توانید پارامترها (تاریخ، زمان، نام فایل) را از طریق دکمه تغییر دهید مواد (خواص).

برای باز کردن فایل یک ماکرو اضافه کنید

اکنون باقی مانده است که راه اندازی ماکرو مورد نیاز خود را در رویداد باز پرونده در کتاب خود آویزان کنیم. برای انجام این کار، کتاب را باز کنید و با استفاده از میانبر صفحه کلید به ویرایشگر ویژوال بیسیک بروید دگرساز+F11 یا دکمه ها ویژوال بیسیک برگ توسعه دهنده (توسعه دهنده). در پنجره ای که در گوشه سمت چپ بالا باز می شود، باید فایل ما را در درخت پیدا کنید و برای باز کردن ماژول دوبار کلیک کنید. این کتاب (این کتاب کار).

اگر این پنجره را در ویرایشگر ویژوال بیسیک نمی بینید، می توانید آن را از طریق منو باز کنید مشاهده - Project Explorer.

در پنجره ماژول که باز می شود، با انتخاب آن از لیست های کشویی در بالا، یک کنترل کننده رویداد باز کتاب اضافه کنید. کارنامه и باز کن، به ترتیب:

یک الگوی رویه باید روی صفحه ظاهر شود. کتاب کار_باز کردن، جایی که بین خطوط زیر خصوصی и پایان دادن به زیر و باید آن دستورات VBA را وارد کنید که باید هنگام باز شدن این کتاب کار اکسل، زمانی که Scheduler آن را طبق برنامه باز می کند، به طور خودکار اجرا شوند. در اینجا چند گزینه مفید برای اورکلاک وجود دارد:

• ThisWorkbook.RefreshAll - تمام پرس و جوهای داده های خارجی، کوئری های Power Query و PivotTables را تازه می کند. همه کاره ترین گزینه. فقط فراموش نکنید که به طور پیش‌فرض اجازه اتصال به داده‌های خارجی را بدهید و پیوندها را از طریق آن به‌روزرسانی کنید فایل – گزینه‌ها – مرکز اعتماد – گزینه‌های مرکز اعتماد – محتوای خارجی، در غیر این صورت وقتی کتاب را باز می کنید یک اخطار استاندارد ظاهر می شود و اکسل بدون به روز رسانی چیزی به صورت کلیک بر روی دکمه منتظر نعمت شما خواهد بود. فعال کردن محتوا (فعال کردن محتوا):

  

• ActiveWorkbook.Connections ("Connection_Name"). Refresh - به روز رسانی داده ها در اتصال Connection_Name.
• Sheets ("Sheet5PivotTables(“PivotTable1«).PivotCache.Refresh - به روز رسانی یک جدول محوری به نام جدول محوری 1 روی ورق Sheet5.
• برنامه. محاسبه - محاسبه مجدد همه کتابهای باز اکسل.
• Application.CalculateFullRebuild - محاسبه مجدد اجباری همه فرمول ها و بازسازی تمام وابستگی های بین سلول ها در همه کتاب های کاری باز (معادل وارد کردن مجدد همه فرمول ها).
• کاربرگ ("گزارش"). PrintOut – چاپ برگه عکس.
• با MyMacro تماس بگیرید – یک ماکرو به نام اجرا کنید MyMacro.
• ThisWorkbook.Save - کتاب فعلی را ذخیره کنید
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace (اکنون، ":"، "-") و "xlsx." - کتاب را در یک پوشه ذخیره کنید د: آرشیو زیر اسم عکس با تاریخ و زمان اضافه شده به نام

اگر می خواهید ماکرو فقط زمانی اجرا شود که فایل توسط Scheduler در ساعت 5:00 صبح باز می شود، و نه هر بار که کاربر کتاب کار را در طول روز کاری باز می کند، پس منطقی است که یک بررسی زمان اضافه کنید، به عنوان مثال:

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

همین. فراموش نکنید که کتاب کار خود را با فرمت ماکرو فعال (xlsm یا xlsb) ذخیره کنید و می توانید با خیال راحت اکسل را ببندید و به خانه بروید و رایانه خود را روشن بگذارید. در یک لحظه معین (حتی اگر رایانه شخصی قفل باشد)، Scheduler اکسل را راه اندازی می کند و فایل مشخص شده را در آن باز می کند و ماکرو ما اقدامات برنامه ریزی شده را انجام می دهد. و در حالی که گزارش سنگین شما به طور خودکار دوباره محاسبه می شود - زیبایی! 🙂

• ماکروها چیست، چگونه از آنها استفاده کنیم، کجا کد ویژوال بیسیک را در اکسل وارد کنیم
• چگونه افزونه ماکرو خود را برای اکسل ایجاد کنیم
• نحوه استفاده از کتاب کار ماکرو شخصی به عنوان یک کتابخانه برای ماکروهای خود در اکسل