If the boss wants a report every day at the same time, don't worry about vacations, sick days, emergencies or even forgetting, you should use the Windows 10 Task Scheduler.
Many of us have recurring tasks that are repeated daily, weekly, monthly, even hourly. Opening a document and completing the same task over and over again is not necessary. If the task can be automated using a VBA procedure, you can execute that procedure from the Windows 10 Task Scheduler. It will happen in the background, and you won't even know. Therefore, if you find repetitive tasks tedious or forget them frequently, try Windows 10 Task Scheduler.
This solution requires Microsoft Visual Basic Scripting Edition (VBScript), but this article provides all the instructions you need. Once you see how versatile this scripting language is, you can explore it more on your own. In addition, we will use the Windows 10 Task Scheduler, but do not worry if this tool is new to you, it is easy to use. This article assumes that you are familiar with Excel and VBA. If you have problems at any time, read the Troubleshooting section at the end of this article.
MORE INFORMATION: Office 365 Prices and features for the consumer
I am using the Office 365 Task Scheduler and Windows 10 on a 64-bit Windows 10 system, but this should work with earlier versions of Office and Windows. You can use the downloadable demo Excel .xlsm, .xls, .bas and .vbs. This technique is not appropriate for the Excel browser edition. Consider where you save the files: you will need that information later.
Figure A shows a simple table object with data and a pivot table. Suppose users enter new records throughout the day, and want to print or email a copy of the updated pivot table every morning at 8 am. Unless users know how to update the pivot table, they may not get updated information. Then, every morning at 8 am, open the file, update the pivot table and then print it or send it via email. We will stick to the basics and print it.
Creating a working VBA procedure in Excel is the first step. The next step is to use Notepad to write a short script that creates an instance of Excel and runs the macro. Finally, we will create a basic task that runs the VBScript at 8 am each morning.
The workbook and procedure
You can easily configure a workbook to update a pivot table when it is opened if the data is in a table object (the demonstration is .xlsm). To do so, right-click anywhere in the pivot table and choose Pivot Table Options in the resulting submenu. In the resulting dialog box, click on the Data tab. Then, check the Update data option when opening the file in the PivotTable Data section ( Figure B ). You don't need to do this for this technique to work, but you should know that you can do it.
At this time, opening the book will update the pivot table, but you still need to print it, that's where the macro comes in. Open the Visual Basic Book Editor (VBE)) by pressing Alt + F11. From the Insert menu, choose Module. In the resulting module, enter the procedure shown in Listing A . Do not try to copy the code of this web page, VBE will not be able to interpret it correctly; instead, write it yourself or import the downloadable .bas file.
The code identifies and selects the pivot table you want to print (PivotTable1 is your default name and I didn't change it). After updating all the objects in the pivot table, the procedure prints the selected pivot table (pivot table 1) in landscape mode. Using the PrintOut method, you can specify multiple copies and so on. To test the procedure, click anywhere inside it and press F5.
Save the workbook and close it. If you are using the tape version, be sure to save the workbook as a macro-enabled file (.xlsm). Write down the location of the workbook and the name of the procedure, because you will need it in the script that we will write next. The demo file is saved locally in the Documents folder.
VBScript is based on Visual Basic and is mainly used by administrators, but it is simple enough that most of us can learn the basics. We will use Notepad as our script editor. Open Notepad and enter the script shown in Listing B .
Set objwb = objApp.Workbook.Open (Object): Users Susan Harkins Documents PivotTable1.xlsm ")
objApp.Visible = False
MsgBox "PivotTable that was printed correctly", vbInformation
This simple script creates an Excel instance, identifies the workbook with the pivot table that we want to print and open that file.With the Visible property set to False, you will not see it.Then, the script runs the macro in Listing A that updates the objects in the pivot table and then prints the pivot table 1. After printing, the script closes the workbook without asking you to save, close the Excel instance and set the Excel object to Nothing.
Save the file as PrintPivotTable1.vbs: you must add the extension. vbs u be yourself when writing the name. Close the file, taking into account its location.
Task Scheduler is a long-time Windows administrative tool that is easy to use and flexible. We will use only a small part of its functionality.
On the Windows Start menu, expand Windows Administrative Tools and select Task Scheduler. To add the task, do the following:
From the Action menu, choose Create Basic Task.
Assign a meaningful name to the task, such as PrintPivotTable1.
Add a brief description ( Figure C ). You may be tempted to skip this step, but it is easy to forget about a task that runs automatically, and others will also benefit. Click Next.
Choose Journal for this task and click Next.
Enter 8:00:00 AM in the Start control on the right. Leave the Repeat setting every 1 (the default), as shown in Figure D . Click Next.
In the Action section, keep the default option, Start a program. Do not change any settings, just click Next.
Earlier I mentioned that you would need to know the location of the file. Use the Browse button to locate PrintPivotTable1.vbs.
In the Action, Start a program control enter C: Windows System32 wscript.exe. This should not be different for most users, this identifies the VBScript program. You may see cscript.exe as you learn more about the language; cscript.exe works from the console. If you cannot find this .exe file, type where wscript in Cortana.
In the Add arguments control, identify the workbook path: "C: Users Susan Harkins Documents PrintPivotTable.vbs", as shown in Figure E . Appointments are mandatory and your route will be different from mine, therefore, update accordingly.
Click Next and then Finish.
To test the script, right-click on the task list and choose Run.
Many things happen, so mistakes are easy. The most likely errors are typographical errors, which do not specify the .vbs, .xlsm, and correct macro names and locations. Fortunately, VBScript will display error messages, so it is easy to locate the actual statement. If you receive an object error message, verify the names of the variables in the script. If you receive an error that Windows cannot find the file or macro, check the names and paths. If the problem seems to be one of the permissions, you may need help from your administrator. I recommend that you save the files locally instead of on a network server while experimenting.
If you choose an odd time, for example, half of the night to print and the task does not occur, check the Task security option. You may want to change the default setting Run only when the user is logged in to Run if the user is logged in or not ( Figure F ).