Makrokomandos paleidimas laiku

A very common case in practice: you need to run one or more of your macros at a given time or at a certain frequency. For example, you have a large and heavy report that updates half an hour and you would like to run the update half an hour before you arrive at work in the morning. Or you have a macro that should automatically send out emails to employees at a specified frequency. Or, when working with a PivotTable, you want it to update on the fly every 10 seconds, and so on.

Let’s look at what Excel and Windows have the ability to implement this.

Running a macro at a given frequency

The easiest way to do this is to use the built-in VBA method Application.OnTimeAn that runs the specified macro at the specified time. Let’s understand this with a practical example.

Open the Visual Basic editor with the button of the same name on the tab ryškalas (Programuotojas) arba spartusis klavišas Kitas+F11, įdėkite naują modulį per meniu Įterpimas – modulis ir nukopijuokite ten šį kodą:

Dim TimeToRun 'global variable where the next run time is stored 'this is the main macro Sub MyMacro() Application.Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cell A1 with a random color :) Call NextRun 'run the NextRun macro to set the next run time End Sub 'this macro sets the time for the next run of the main macro Sub NextRun() TimeToRun = Now + TimeValue("00:00:03") 'add 3 seconds to the current time Application.OnTime TimeToRun, "MyMacro" 'schedule the next run End Sub 'macro to start the repeat sequence Sub Start() Call NextRun End Sub 'macro to stop the repeat sequence Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Let’s figure out what’s what here.

First, we need a variable that will store the time of the next run of our macro – I called it TimeToRun. Please note that the contents of this variable must be available to all our subsequent macros, so we need to make it bendras, i.e. declare at the very beginning of the module before the first Pagal.

Next comes our main macro MyMacro, which will perform the main task – to recalculate the book using the method Taikymas.Apskaičiuokite. To make it clearer, I added the formula =TDATE() to the sheet in cell A1, which displays the date and time – when recalculated, its contents will be updated right before our eyes (just turn on the display of seconds in the cell format). For extra fun, I also added to the macro the command to fill cell A1 with a randomly selected color (the color code is an integer in the range 0..56, which is generated by the function Rnd and rounds up to an integer function Žiniasklaida).

Makro NextRun adds to the previous value TimeToRun 3 more seconds and then schedules the next run of the main macro MyMacro for this new time. Of course, in practice, you can use any other time intervals you need by setting the function arguments TimeValue in the format hh:mm:ss.

And finally, just for convenience, more sequence launch macros have been added. Pagrindinis and its completion apdaila. The last one uses the fourth method argument to break the sequence. Laiku lygus Klaidingas.

Total if you run the macro Pagrindinis, then this whole carousel will spin, and we will see the following picture on the sheet:

You can stop the sequence by running, respectively, the macro apdaila. For convenience, you can assign keyboard shortcuts to both macros using the command Makrokomandos – parinktys kortelė ryškalas (Developer — Macros — Options).

Running a macro on a schedule

Of course, everything described above is possible only if you have Microsoft Excel running and our file is open in it. Now let’s look at a more complicated case: you need to run Excel according to a given schedule, for example, every day at 5:00, open a large and complex report in it and update all connections and queries in it so that it will be ready by the time we arrive at work 🙂

In such a situation, it is better to use Windows Scheduler – a program specially built into any version of Windows that can perform specified actions on a schedule. In fact, you are already using it without knowing it, because your PC regularly checks for updates, downloads new anti-virus databases, synchronizes cloud folders, etc. It’s all the work of the Scheduler. So our task is to add to the existing tasks another one that will launch Excel and open the specified file in it. And we will hang our macro on the event Darbaknygė_Atidaryti this file – and the problem is solved.

I want to warn you right away that working with the Scheduler may require advanced user rights, so if you cannot find the commands and functions described below on your work computer in the office, contact your IT specialists for help.

Launching the Scheduler

So let’s start the Scheduler. To do this, you can either:

• Right click on the button pradžia Ir pasirinkti Kompiuterio valdymas (Computer management)
• Select in Control Panel: Administration – Task Scheduler (Control Panel — Administrative Tools — Task Scheduler)
• Pasirinkite pagrindiniame meniu Start – Accessories – System Tools – Task Scheduler
• Paspauskite klaviatūros nuorodą Laimėti+R, įveskite taskschd.msc ir paspauskite įeiti

The following window should appear on the screen (I have an English version, but you can also have a version):

Sukurkite užduotį

To create a new task using a simple step-by-step wizard, click on the link Create a simple task (Create Basic Task) dešiniajame skydelyje.

At the first step of the wizard, enter the name and description of the task to be created:

Spustelėkite mygtuką Sekantis (Kitas) and in the next step we select a trigger – the launch frequency or an event that will launch our task (for example, turning on the computer):

If you chose Kasdien (Kasdien), then in the next step you will need to select a specific time, start date of the sequence and step (every 2nd day, 5th day, etc.):

The next step is to choose an action – Vykdykite programą (Start a program):

And finally, the most interesting thing is what exactly needs to be opened:

Lauke Program or script (Program/script) you need to enter the path to Microsoft Excel as a program, i.e. directly to the Excel executable. On different computers with different versions of Windows and Office, this file may be in different folders, so here are a few ways for you to find out its location:

• Right-click on the icon (shortcut) to launch Excel on the desktop or in the taskbar and select the command medžiagos (Ypatybės), and then in the window that opens, copy the path from the line Taikinys:

                       

• Open any Excel workbook, then open Task Manager " (Užduočių tvarkyklė) Stumti "Ctrl"+Kitas+Kaina nuo and by right clicking on the line "Microsoft Excel", choose a command medžiagos (Ypatybės). In the window that opens, you can copy the path, not forgetting to add a backslash to it and EXCEL.EXE at the end:

               

• Open Excel, open Visual Basic editor with keyboard shortcut Kitas+F11, open panel Tiesioginis derinys "Ctrl"+G, enter the command into it:

  ? Application.Path

  … and click on įeiti

  

  Copy the resulting path, not forgetting to add a backslash to it and EXCEL.EXE at the end.

Lauke Pridėti argumentų (neprivaloma) (Add arguments (optional)) you need to insert the full path to the book with the macro that we want to open.

When everything is entered, then click Sekantis ir tada apdaila (Baigti). The task should be added to the general list:

It is convenient to manage the created task using the buttons on the right. Here you can test the task by running it immediately (bėgti)without waiting for the specified time. You can temporarily deactivate a task (Išjungti)so that it stops running for a period of time, such as your vacation. Well, you can always change the parameters (dates, time, file name) through the button medžiagos (Ypatybės).

Add a macro to open a file

Now it remains to hang in our book the launch of the macro we need on the file open event. To do this, open the book and go to the Visual Basic editor using the keyboard shortcut Kitas+F11 arba mygtukai "Visual Basic" kortelė ryškalas (Programuotojas). In the window that opens in the upper left corner, you need to find our file on the tree and double-click to open the module Ši knyga (Ši darbo knyga).

If you do not see this window in the Visual Basic editor, then you can open it through the menu Rodinys – Project Explorer.

In the module window that opens, add a book open event handler by selecting it from the drop-down lists at the top Darbaknygė и Atvirasatitinkamai:

A procedure template should appear on the screen. Darbaknygė_Atidaryti, where between the lines Private Sub и End Sub and you need to insert those VBA commands that should be automatically executed when this Excel workbook is opened, when the Scheduler opens it according to the schedule. Here are some useful options for overclocking:

• ThisWorkbook.RefreshAll – Refreshes all external data queries, Power Query queries, and PivotTables. The most versatile option. Just don’t forget to allow connections to external data by default and update links via File – Options – Trust Center – Trust Center Options – External Content, otherwise, when you open the book, a standard warning will appear and Excel, without updating anything, will wait for your blessing in the form of clicking on the button Įgalinti turinį (Įgalinti turinį):

  

• ActiveWorkbook.Connections(“Connection_Name”).Refresh — updating data on the Connection_Name connection.
• Sheets(“Sheet5“).PivotTables(“PivotTable1«).PivotCache.Refresh – updating a single pivot table named PivotTable1 ant lapo Sheet5.
• Taikymas.Apskaičiuokite – recalculation of all open Excel workbooks.
• Application.CalculateFullRebuild – forced recalculation of all formulas and rebuilding of all dependencies between cells in all open workbooks (equivalent to re-entering all formulas).
• Worksheets(“Report”).PrintOut – print sheet nuotraukos.
• Call MyMacro – run a macro named MyMacro.
• ThisWorkbook.Save – save the current book
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – save the book to a folder D:Archive vardu nuotraukos with date and time appended to the name.

If you want the macro to be executed only when the file is opened by the Scheduler at 5:00 am, and not every time the user opens the workbook during the working day, then it makes sense to add a time check, for example:

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

That’s all. Don’t forget to save your workbook in a macro-enabled format (xlsm or xlsb) and you can safely close Excel and go home, leaving your computer on. At a given moment (even if the PC is locked), the Scheduler will launch Excel and open the specified file in it, and our macro will perform the programmed actions. And you will luxuriate in bed while your heavy report is automatically recalculated – beauty! 🙂

• What are macros, how to use them, where to insert Visual Basic code in Excel
• How to create your own macro add-in for Excel
• How to use the Personal Macro Workbook as a library for your macros in Excel