Lots of us have recurring tasks that are repeated daily, weekly, monthly—even hourly. Opening a document and completing the same task over and over isn’t necessary. If the task can be automated using a VBA procedure, you can run that procedure from Windows 10 Task Scheduler. It’ll happen in the background, and you won’t even know it. So, if you find repetitive tasks tedious or you frequently forget them, 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 script language is, you can explore it further on your own. In addition, we’ll use Windows 10 Task Scheduler, but don’t worry if this tool is new to you, it’s easy to use. This article does assume you’re familiar with Excel and VBA. If you run into trouble at any point, read the Troubleshooting section at the end of this article.
LEARN MORE: Office 365 Consumer pricing and features
I’m using Office 365 and Windows 10 Task Scheduler on a Windows 10 64-bit system, but this should work with earlier versions of Office and Windows. You can use the downloadable demonstration Excel .xlsm, .xls, .bas, and .vbs files. This technique isn’t appropriate for Excel’s browser edition. Note where you save files–you’ll need that information later.
Figure A shows a simple Table object with data and a PivotTable. Let’s suppose users enter new records throughout the day, and you want to print or send by email a copy of the updated PivotTable every morning at 8 am. Unless users know to refresh the PivotTable, you might not get up-to-date information. So, every morning at 8 am, you open the file, refresh the PivotTable, and then print or email it. We’ll stick to the basics and just 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’ll create a basic task that runs the VBScript at 8 am every morning.
The workbook and procedure
You can easily configure a workbook to update a PivotTable when opening if the data is in a Table object (the demonstration .xlsm is). To do so, right-click anywhere in the PivotTable and choose PivotTable Options from the resulting submenu. In the resulting dialog, click the Data tab. Then, check the Refresh Data When Opening The File option 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.
Right now, opening the workbook will refresh the PivotTable, but you still need to print it–that’s where the macro comes in. Open the workbook’s Visual Basic Editor (VBE) by pressing Alt+F11. From the Insert menu, choose Module. In the resulting module, enter the procedure shown in Listing A. Don’t try to copy the code from this web page–VBE won’t be able to interpret it correctly; instead, type it yourself or import the downloadable .bas file.
'Update and then print PivotTable1.
Dim pt As PivotTable
Set pt = Sheets("PivotTable").PivotTables("PivotTable1")
'Refresh all pivottables.
'Set landscape mode.
ActiveSheet.PageSetup.Orientation = xlLandscape
The code identifies and selects the PivotTable you want to print (PivotTable1 is its default name, and I didn’t change it). After refreshing all the PivotTable objects, the procedure prints the selected PivotTable (PivotTable1) 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’re using the ribbon version, be sure to save the workbook as a macro-enabled file (.xlsm). Note the location of the workbook and the procedure’s name, because you’ll need that in the script we’ll write next. The demonstration file is saved locally in the Documents folder.
VBScript is modeled on Visual Basic and used mostly by administrators, but it’s simple enough that most of us can learn the basics. We’ll use Notepad as our script editor. Open Notepad and enter the script shown in Listing B.
'Print PivotTable1 in Excel workbook, PivotTable1.xlsm.
Set objApp = CreateObject("Excel.Application")
Set objwb = objApp.Workbooks.Open("C:UsersSusan HarkinsDocumentsPivotTable1.xlsm")
objApp.Visible = False
Set objApp = Nothing
MsgBox "PivotTable printed successfully", vbInformation
This simple script creates an Excel instance, identifies the workbook with the PivotTable we want to print, and opens that file. With the Visible property set to False, you won’t see it. Next, the script runs the macro in Listing A that refreshes the PivotTable objects and then prints PivotTable1. After printing, the script closes the workbook without prompting to save, closes the instance of Excel, and sets the Excel object to Nothing.
Save the file as PrintPivotTable1.vbs—you must add the .vbs extension yourself when typing the name. Close the file, noting its location.
Task Scheduler is a Windows administrative tool that’s been around for a long time–it’s easy to use, and it’s flexible. We’ll use only a small part of its functionality.
From 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.
- Give the task a meaningful name, such as PrintPivotTable1.
- Add a short description (Figure C). You might be tempted to skip this step, but it’s easy to forget about a task that runs automatically, and others will benefit as well. Click Next.
- Choose Daily for this task and click Next.
- Enter 8:00:00 AM in the Start control to the right. Leave the Recur Every setting at 1 (the default), as shown in Figure D. Click Next.
- In the Action section, retain the default option, Start a program. Don’t change any settings–just click Next.
- I mentioned earlier that you’d need to know the file’s location. Use the Browse button to locate PrintPivotTable1.vbs.
- In the Action, Start a Program control enter C:WindowsSystem32wscript.exe. This shouldn’t be different for most users–this identifies the VBScript program. You might see cscript.exe as you learn more about the language; cscript.exe works from the console. If you can’t find this .exe file, type where wscript into Cortana.
- In the Add arguments control, identify the workbook path: “C:UsersSusan HarkinsDocumentsPrintPivotTable.vbs”, as shown in Figure E. The quotes are required, and your path will differ from mine, so update accordingly.
- Click Next and then Finish.
To test the script, right-click it in the task list and choose Run.
There’s a lot going on, so mistakes are easy. The most likely error are typos—not specifying the correct .vbs, .xlsm, and macro names and locations. Fortunately, VBScript will display error messages, so pinpointing the actual statement is easy. If you get an object error message, check variable names in the script. If you get an error that Windows can’t find the file or the macro, check names and paths. If the problem seems to be one of the permissions, you might need help from your administrator. I recommend that you save the files locally instead of on a networked server while you experiment.
If you pick an odd time, for instance, the middle of the night to print and the task doesn’t occur, check the task’s Security option. You might want to change the default Run only when user is logged on setting to Run whether user is logged on or not (Figure F).
Although VBScript supports error handling, there’s none in this simple script. If you work with network printers that might go offline unexpectedly or have other issues, you might want to add appropriate error handling.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.