How To Create A Scheduled Task To Run Powershell Script

If the boss wants a report every day at the same time, don't worry about vacations, sick days, emergencies, or even forgetfulness, you should use Windows 10's Task Scheduler.

Image: Andrey Popov, Getty Images/iStockphoto

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.

The task

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.

Figure A

We'll automate a task that prints the updated PivotTable every morning at 8 am.

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.

Figure B

You can force Excel to refresh PivotTable objects when you open the workbook.

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.

Listing A

Sub PrintUpdatedPivotTable()

'Update and then print PivotTable1.

Dim pt As PivotTable

Set pt = Sheets("PivotTable").PivotTables("PivotTable1")

'Select PivotTable1.

pt.TableRange1.Select

'Refresh all pivottables.

ThisWorkbook.RefreshAll

'Set landscape mode.

ActiveSheet.PageSetup.Orientation = xlLandscape

'Print PivotTable1.

ActiveWindow.SelectedSheets.PrintOut

End Sub

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.

The script

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.

Listing B

'Print PivotTable1 in Excel workbook, PivotTable1.xlsm.

Set objApp = CreateObject("Excel.Application")

Set objwb = objApp.Workbooks.Open("C:\Users\Susan Harkins\Documents\PivotTable1.xlsm")

objApp.Visible = False

objApp.Run "PrintUpdatedPivotTable"

objwb.Close False

objApp.Quit

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

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:\Windows\System32\wscript.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:\Users\Susan Harkins\Documents\PrintPivotTable.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.

Figure C

Figure D

Enter the time you want to print the PivotTable.

Figure E

Identify the script language and the workbook path.

To test the script, right-click it in the task list and choose Run.

Troubleshooting

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).

Figure F

Change this option if you want the task to run even if you're not logged on.

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 susansalesharkins@gmail.com.

Microsoft Weekly Newsletter

Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays

Also see

How to add a looping introduction to a PowerPoint presentation (TechRepublic)
How to add horizontal lines to a Word 2016 document (TechRepublic)
How to add a drop-down list to an Excel cell (TechRepublic)
50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic)
Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
Microsoft Office has changed, how you use it should too (ZDNet)
Best cloud services for small businesses (CNET)
Best to-do list apps for managing tasks on any platform (Download.com)
More must-read Microsoft-related coverage (TechRepublic on Flipboard)