PowerShell - Run Macros, Copy Files, Do Cool Stuff... with power?

What am I talking about?

I have created multiple ways to move things around Windows and launch Macros on different workbooks. For example, I have used task scheduler to launch Auto_Run macros that then do whatever I please. Copying files with Batch script is another one.

These all work, but there are problems. Using an Excel workbook to stage your scripts in inefficient as Excel is resource intensive and error prone - plus you then can't close the Excel application. Windows Batch is not user friendly and you can only achieve simple tasks - for example you need an essay of code to add timestamps to file names you copied. 

PowerShell is a task automation developed by Microsoft (which means it is built into Windows) which is simply the best way to manage the tasks I mentioned above. And you can with Powershell close Excel.

Open it up, take a look

Windows PowerShell looks like command prompt (this is what actually runs the commands), PowerShell ISE is the scripting editor, so open that (right click and run as administrator) and let's have a play. 

Quickly configure to View Menu to match mine, all you should have to do is click "Show Script Pane", I didn't change anything else from default. You can enable the "Commands" pane under the "Add-ons" menu if you don't have it (again I didn't add this, should be default). 

With the editor, you can write in your script in the top half (just like a notepad with Batch) and then watch it run in the bottom half. Aren't you already excited?

First you need to allow PowerShell to run scripts. Microsoft have made it difficult to run, in fear of a hacker running scripts on your PC. They assume hackers would be too dumb to follow the next steps:

1. In the bottom box, copy in Set-ExecutionPolicy RemoteSigned
2. Push enter, and click yes to all in the pop up boxes
3. Assuming you remembered to open PowerShell as an admin, we are now good to go. 

Open Excel Workbook, Run Macro, Then Close

This is very simple!

In the top half, copy in the following script:

# start Excel 
$excel = New-Object -comobject Excel.Application  

#open file 
$FilePath = 'C:\Users\Username\Desktop\ExcelWorkbook.xlsm' #<------- Change this!!! 
$workbook = $excel.Workbooks.Open($FilePath)  

#make it visible (just to check what is happening) 
$excel.Visible = $true  

#access the Application object and run a macro $app = $excel.Application 
$app.Run("Macro") #<------- Change this!!! 
$excel.Quit()     

#Popup box to show completion - you would remove this if using task scheduler 
$wshell = New-Object -ComObject Wscript.Shell $wshell.Popup("Operation Completed",0,"Done",0x1)  

exit 

Self explanatory by reading the comments, but just make sure you change the File path and Macro name. Pushing the green run button up the top, should now load your Excel File, run the macro, wait for it finish, close Excel and display a popup box, finally close PowerShell. 

Make sure your Excel macro has Application.DisplayAlerts = False else you will have to click buttons like that pop up during your Excel macro. You can then change line 9 to false to not even show Excel when running.

Now save the script somewhere. Follow the process in my macro scheduling tutorial, only difference is in the "Action" tab of setting up the task. 

Program/Script = 
Powershell.exe
Add Arguments = 
-ExecutionPolicy Bypass C:\Users\username\Desktop\excel.ps1

Simple as that.

Note - you can't double click on the ps1 file to run it. You need to right click and "run with powershell" to test it out. 

How to copy and backup files?

This is a simple PowerShell task, let’s copy files from one location to another and insert a timestamp.

write-host "Copying Files!" 

#Gets Time
$timestamp = Get-Date -Format "dd-MMM-yyyy HH-mm-ss"

#Define File and Copy Location
$file = "C:\CopyCopy\ExcellenCopy.txt"
$copyto = "C:\CopyCopy\CopyInhere\ExcellenCopy "+$timestamp+".txt"

#Copy File
Copy-Item $file -Destination $copyto

Change the variables $file and $copyto to your required locations. Work around the $timestamp variable.
This great script spits out the following:

Now you have what you need to start your PowerShell journey! Good luck!

Try a slightly more advanced tutorial, using PowerShell to detect internet connectivity.