In this article, we will review methods to incorporate repetitions to our scripting code. Our goal will be to read information from a spreadsheet, use that information, and then store extra data within the source spreadsheet. We will step through all the rows in the spreadsheet utilizing the same process on each entry.
Before we get into adding new code, we need to discuss a few more concepts. Firstly, we will look at a concept called a Function. A function can be thought of as a standardized task. This is akin to giving someone a page and asking them to make a copy of the page and then you get 2 copies in return. No matter whats on the page, or information you process, the function stays the same, take, copy, return. In programming, the page to copy would be referred to as an argument or a parameter for the function, the copied pages would be referred to as the return value of the function. In pseudo code it may look like something below;
Func CopyPage($oThePage) $oCopies = $oPage & $oPage Return $oCopies End Func Put the copies in a container; $oMyCopies = CopyPage($oThePage)
Arrays;
Let’s look at another type of container for information, the Array. As we have discussed variables as containers, let’s think of arrays as cubby holes for our container to sit in. For this variable type we will need to define it or analogously, we need to construct our empty cubby holes. This is done simply as below;
Dim $aCubbyHoles[4] Dim $aCubbyHoles[4][4] This makes 4 rows This makes 4 rows of cubbys of 4 columns of cubbys [] [][][][] [] [][][][] [] [][][][] [] [][][][] Please note that referencing the cubbyholes by index requires that you count from 0 upwards. The last row above is row #3, which somewhat confusingly is the 4th and final row, this is referred to as the Upper Bounds of the array. If we need to know how big the array is we use UBound($aTheArray) [= 4] The last row can be referenced as UBound($aTheArray) - 1 [= 3] We can also request the size of the array's rows and columns; UBound($aTheArray, $UBOUND_ROWS) UBound($aTheArray, $UBOUND_COLUMNS) Lastly, An empty cubbyhole, ready for us to construct on the fly that we can expand as required; Dim $aCubbyHoles[0]
Repetitions:
Now we need a way to use this new function on all of our entries. We accomplish this through another concept called loops. There are numerous ways to make loops depending on your goals, we will look at a couple of simple implementations.
We will be processing an unknown amount of entries so we will make a loop that continues while there is data to process and end the loop when we come across an empty entry. This type of loop is referred to as a While Loop and the pseudo code below will highlight its usage.
Dim $aMyPages[0] While $oThePage <> Nil $oMyCopies = CopyPage($oThePage) _ArrayAdd($aMyPages,$oMyCopies) WEnd
This loop will continue as long as there are pages to copy and add them to our array $aMyCopies. We are utilizing a new command, _ArrayAdd(Array, Value to add), to expand our cubbyholes and place a piece of information within the newly created cubbyhole. The keyword nil means the value is nothing, the operator <> means “not equal to”. When this loop ends the value of $oMyPages will be a collection of all the pages copied.
The next loop type we will discuss is called a For loop. This type of loop will look at collections of information, like an array, and will iterate through each entry. The loop is highlighted in the pseudo-code below;
For $oPage in $aMyCopies Process $oPage Next
Lets now use the information above in a practical manner by continuing to add functionality to the scripting code we started to modify in the previous article.
Our goal will be to read some information from Microsoft Excel and use it in a loop to automate a large amount of repetitive work.
Below is sample spreadsheet data where A1 = “ProjID”, B1 = “Date”
A B 1 ProjID Date 2 123-ABCD 3 124-ABCD 4 125-ABCD 5 6 7 8
We will now add the scripting code below to read the above sample data from an open Excel instance with this data on the currently displayed sheet. We will utilize a new function, _Excel_RangeRead ( $oWorkbook , $vWorksheet, $vRange), to read the range above , A2:B8. We will exclude row 1 as it only contains the data headers. This function will return an array of values, or analogously, it will return a set of cubbyholes with information in them for us to examine and use, in this instance, we will be working with 7 rows and 2 columns.
For this example, we will use a slightly different way to use a For Loop. We will be telling the Loop to iterate a number of times equal to the number of rows in our array. Remembering that the first index in our array is 0 we will create a loop as below;
#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:B8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
For $i = 0 to UBound($aProjData, $UBOUND_ROWS) - 1
$sCurrProjID = $aProjData[$i][0]
Create_TextFile($sCurrProjID, $sCurrDate)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
Next
Func Create_TextFile($sTheID, $sTheDate)
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Send($sMessage)
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
$sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Completed.txt{ENTER}"
Send($sSaveDir)
EndFunc
You can see the modified script above. For the first modification, we are setting a variable for the current date. This is intentionally outside of the loop for the reason that it does not need to be calculated each loop. This will slow the loop and it is good practice to keep things like the number of calculations in a loop in mind and how to reduce the number of calculations.
In our above loop we have set a new variable $i, as an integer ranging from 0 to the number of row elements within the array. Every time the loop completes $i will automatically increase by 1 until it reaches the requested number.
The next line puts into a new variable the value stored in our array in the row $i and column 0. This is the first row of the Excel data, the ProjID.
The next line calls our new function (bottom of code) and uses the 2 variables we have created for arguments.
The next line is used to write a value, the completed date, into excel. You will notice that we are placing the values in Excel column B and in row $i + 2. Remembering that $i = 0 on the first loop we need to ensure that the first Excel entry goes into B2, the next in B3, and so forth. This is accomplished by adding 2 to the current value of $i.
Exploring our new function Create_TextFile($sTheID, $sTheDate)
Comparing the new function with the old script routine we can see again that the changes are minimal. Most notably is the method we obtain the Project ID and current date values. You may notice that we called the command with the arguments ($sCurrProjID, $sCurrDate) but the function has the arguments ($sTheID, $sTheDate). This is to allow for you to name your variables random names and allows the function to have uniform variable names.
You may also notice the inclusion of the word “local” before a couple of the variable’s names. This lets the script know that we will not use these values outside of this function. The program will automatically discard this temporary data as required.
Func Create_TextFile($sTheID, $sTheDate)
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
Local $sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Send($sMessage)
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
Local $sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Completed.txt{ENTER}"
Send($sSaveDir)
EndFunc
\/ \/ \/ OLD ROUTINE BELOW \/ \/ \/
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 only issue that remains is to handle empty entries found in the Excel data @ A5. We will explore 2 possibilities, the first is a minor modification to the above scripting code and introduces a new concept.
The IF statement, A fork in the road.
There will be times that you will need to verify the data you are using before you start to process it. One of these methods is by using an IF statement. This simply will give you the power to instruct the code to choose a path based on a question.
Using If statements effectively can help reduce the number of calculations required, lets see the example below where we will use an IF statement to skip all the calculations when the variable $sTheID is empty.
Func Create_TextFile($sTheID, $sTheDate)
If $sTheID <> "" Then
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
Local $sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Send($sMessage)
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
Local $sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Completed.txt{ENTER}"
Send($sSaveDir)
EndIf
EndFunc
The above code simply checks to see if the value of $sTheID is not equal to “”, (an empty text string of no length). If the value is not equal then all of the indented code below the IF statement is executed. If it is equal to “”, then the code skips down to the EndIf.
While this will save us from processing extra unnecessary steps, it still allows the function Create_TextFile to be called when there is no data to process. Let’s look at how we can fix that upstream in the code before this function is called.
At the start of the article the While Loop was mentioned and this will be a great example of its usage.
#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:B8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
$i = 0
$sCurrProjID = $aProjData[0][0]
While $sCurrProjID <> ""
Create_TextFile($sCurrProjID, $sCurrDate)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
$i = $i + 1
$sCurrProjID = $aProjData[$i][0]
Next
Func Create_TextFile($sTheID, $sTheDate)
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Send($sMessage)
WinClose("Untitled - Notepad")
WinWaitActive("Notepad", "Save")
$sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Completed.txt{ENTER}"
Send($sSaveDir)
EndFunc
As you can see, again we only slightly modified our code. First, we defined an integer variable to $i and then set the initial value of $sCurrProjID to the first entry in Excel. Next, we start a While Loop that continues as long as the current project ID is not an empty string. Once the While Loop fails it’s conditional test the loop will end and will not try to process any entries below the first empty value.
Within the loop at the end of the statement, we need to manually increase the value of $i by 1 and retrieve the next project ID so that the While statement can see if it is empty.
Also, we no longer need the IF statement within the function Create_TextFile() as we will never call the function if we find an empty value for the project ID.
We now have a script that will read data from within Excel, processes that data and create additional files, and when completed adds a completed date within Excel at a location of our choosing. This code will work as many times as needed and process as many entries at once as we specify when defining the variable $aProjData.
Topics Covered;
A-1) Varaibles; definitions and naming conventions
A-2) Gathering data from the user
A-3) Using macros [ie; @CRLF @MDAY]
A-4) String construction with the operator &
A-5) Send(); sending input to applications
A-6) Special control strings [ie; {ENTER} "^n"]
A-7) Automating Microsoft Excel
A-8) Object Oriented Programming
B-1) Functions and their structure and usage
B-2) Arrays; 1 Dimensional and 2 Dimensional
B-3) Repititions and Loops
B-4) While ... WEnd Loop
B-5) Adding to Arrays, Setting and retriving array data
B-6) For ... Next Loop
B-7) Read and Write in Excel
B-8) If ... END statement