In the course of operating a sole-proprietary based business, I have had to learn to find advantages to remove time hungry tasks from a dense production schedule. In fact, I have found there are many tasks we perform in our daily business lives that are tedious and time-consuming. The more time we divert to these overhead type tasks the less time there is for profitable production.
Some examples of tasks that can be automated are for the end of projects; compressing files, storing files in a new location, sending a completion email, completing and printing documentation or running reports.
Now before you say you don’t know programming lets look at the language we will be exploring in this article. This particular method of automation uses a scripting language and it features easier to understand command names. For example the command WinActivate; this is used to Activate a Window [A window being any windows program running], the command Send is used to send keystrokes to simulate someone typing, MouseMove and MouseClick are used to simulate a user operating the mouse.
If you have been interested in programming or program logic flows and want an effective way to practice some practical uses of logic then windows scripting is a great place to start. For this discussion, we will use the free editor SciTE and the free to use language Auto-It. [ https://www.autoitscript.com/site/ ]
Using one of Auto-It’s online examples on notepad automation, we will examine their example code below:
[ https://www.autoitscript.com/autoit3/docs/tutorials/notepad/notepad.htm ]
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
Send("This is some text.")
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
Send("!n")
Looking at the code above we can see that;
Line 01, Windows is being asked to Run NotePad.exe.
Line 02, The code waits until the window is active for NotePad.
Line 03, Some information is placed within the text file using the Send() command.
Line 04, NotePad.exe 's window is closed.
Line 05, The code waits for a window to become active that has "Save" as a title.
Line 06, The code uses Send() to simulate an Alt-N key press.
Variables – Information Containers
Let’s look at a concept called variables. For programming, variables are containers that can hold various types of information. The plus side of a scripting language is that you can shove anything into a container without worrying a lot about the type of container your data is going in, the scripting language will try to figure that out for you.
Our first variable will be a string of text. As such we will use a small “s” as a prefix so we remember what kind of value the variable should have. We will use “$sMessage” for the variable name and put a text string within it. “$” is the prefix for all variables in this scripting language.
$sMessage = "Project XXX-ZZZZ Completed: MM/DD/YYYY"
As you can see we are now setting up a simple Notice of Completion message to use as a template and now we need to get some relevant information into the placeholders; XXX-ZZZZ, MM, DD, YYYY.
First, let’s look at getting information from the user using the InputBox command. The command’s syntax is [ InputBox ( “title”, “prompt”) ] where “title” can be anything and “prompt” is used to ask for the desired information. It is used as shown below;
$sProjID = InputBox("Alert!","Please Enter Project ID.")
Now we have used a command to place a value within our container $sProjID by asking the user for information. Next, Let’s examine another method to gather data for our message. Macros are a part of the languages variables that are always filled with relevant information. The ones we are interested in are the date macros @MDay @MON and @YEAR. All macros have the prefix “@”.
Now we have 4 pieces of information and need to combine them to create our completion notice text. This is done simply with the “&” operator, which gives the instruction to add the next text after “&” to the proceeding text string. We can then set the completion message as shown below;
$sMessage = "Project " & $sProjectID & " Completed: " & @MDAY & "/" & @MON & "/" & @YEAR & "."
Let’s revisit the code all together to see that the increase in complexity was very slight.
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$sProjID = InputBox("Alert!","Please Enter Project ID.")
$sMessage = "Project " & $sProjectID & " Completed: " & @MDAY & "/" & @MON & "/" & @YEAR & "."
Send($sMessage)
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
Send("!n")
You can see that wee added 2 lines of code that we discussed and also modified the Send() command to use the text string stored in our container variable $sMessage.
Let’s look at storing this text file in a specific directory with a specific name to use in the second Send() command. For simplicity, we will imagine there is a folder within “MyDocuments” named “Completed” that these files will be stored within for record.
Utilizing another available macro @MyDocumentsDir and the string combining operator & we can point to this directory and store it within its own container.
Nex, we will reuse the gathered Project ID from above to make a unique filename.
For this instance, we will use the Enter key to accept our created filename during the save process. This can be done within the created string and is processed in the Send() command. This can be done simply by using {ENTER} within the text string as shown below in the completed statement:
$sSaveDir = @MyDocumentsDir & "\Completed\{ENTER}"
Revisiting the code we can now incorporate the automated naming and saving method above.
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$sProjID = InputBox("Alert!","Please Enter Project ID.")
$sMessage = "Project " & $sProjectID & " Completed: " & @MDAY & "/" & @MON & "/" & @YEAR & "."
Send($sMessage) WinClose("Untitled - Notepad") WinWaitActive("Notepad", "Save")
$sSaveDir = @MyDocumentsDir & "\Completed\" & $sProjID & "_Completed.txt{ENTER}"
Send($sSaveDir)
The value in this may not be seen as we are looking at a single line text document, however, this is only the briefest of examples. Form letters can be crafted that gather even more data automatically providing for rich, data-filled documents to be crafted with little to no effort at all.
One of the advantages of automation is the wide range of uses you can garner with your data once collected. Let’s look at reusing this collected data in a second application. This time utilizing a spreadsheet within Microsoft Excel.
First the new code as a reference:
#include <Excel.au3>
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$sProjID = InputBox("Alert!","Please Enter Project ID.")
$sMessage = "Project " & $sProjectID & " Completed: " & @MDAY & "/" & @MON & "/" & @YEAR & "."
Send($sMessage) WinClose("Untitled - Notepad") WinWaitActive("Notepad", "Save")
$sSaveDir = @MyDocumentsDir & "\Completed\" & $sProjID & "_Completed.txt{ENTER}"
Send($sSaveDir)
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite ( $oWorkbook, $oWorkbook.Activesheet, $sMessage, "B2")
We have added 4 lines to the code. Line #1 tells the language to include the instructions on working with Excel. The bottom 3 lines perform the following functions; 1) Open the program Excel, or check to see if Excel is already open. 2) Open a new workbook. 3) Inserts the data into the workbook on the active worksheet in the cell B2.
Now we have the start of a function that creates a customized message and enters data into a spreadsheet. There are a large amount of opportunities in these few commands and the world of scripting has so much more to offer.
Object-Oriented Programming
A note on the variable prefix “o”. This signifies that our expected value is going to be an object. For notepad, we used the command Run() which simply starts a program. For Excel, the program was opened and stored in the container $oExcel.
Each variable types have their own uses and objects come with added functionality as well. As shown, we created a new workbook and stored that object in the container $oWorkbook. In programming, an object may have many pieces of information attached to it. A piece of the ready-made information attached to the object $oWorkbook is the currently active worksheet in use and it can be accessed and referenced from within the container we created as such; $oWorkbook.ActiveSheet. Utilizing objects is a subset of programming that is very common today called Object Oriented Programming OOP.
Utilizing OOP methods it is possible to utilize the host applications internal commands and features. This is especially useful when you need the functionality of one program to format data or calculate numbers for use in a secondary program. Communicating directly with the intended program is far more reliable than the usage of the Send() and MouseMove() type commands.
To end this introduction to automation I will briefly mention the possibilities to complete almost any needed functionality from interacting with web services, Http(s), Ftp, Shtml, JavaScript to interacting with any Windows program. Auto-It has many abilities and only putter out of power around the time you should be working with an actual more robust programming language. https://www.autoitscript.com/site/autoit/
Continue exploring in Part B where loops and if statements are discussed as well as reading and writing multiple values to an Excel spreadsheet.