In this segment of introductory programming and automation, we will be discussing the use of MouseClick to interact with an Internet Explorer web browser and move to more reliable, quicker, and more robust methods of interacting with applications.
New loop types and organizational methods are discussed as well as topics dealing with the reduction of calculations, instructions and overall runtime. A look at data and file formats brings us to create KML files utilizing XML’s serialized data template.
The code from last segment, our starting point ;
#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$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
We will add (2) columns of data below for GPS coordinates to the source data.
A B C D E 1 ProjID Date Lat Lon Map URL 2 123-ABCD 05.248 -35.874 3 124-ABCD 60.147 -20.147 4 125-ABCD 35.354 -05.657 5 6 7 8
In this segment, we will add new functions to handle some additional information we will add to the source data below. But first, there is a tool that provides a great deal of useful information in automating windows, the AutoIt Window Info Tool.
Auto-IT Window Info Tool
Using the Auto-It Window Info Tool we can gather coordinates to use with MouseClick.
This is a very handy tool that is installed along with Auto-It. This tool will give you the coordinates of your mouse, the name of controls and applications that will aid in automating any windows functionality. One basic piece of information is the Title data. This can be used with calls to WinWait, WinActivate. For this example, we are gathering the window coordinates of the address bar in Internet Explorer.
With this information, we can simulate a user’s input with a mouse. Looking at the below code you will see that the coordinates 250,50 is used within the MouseClick method.
The new function we will be adding is below;
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
Local $oIE = _IECreate("https://www.google.com/maps/search/" & sGPS )
AutoItSetOption("MouseCoordMode", 0)
MouseClick($MOUSE_CLICK_PRIMARY, 250, 50)
MouseClick($MOUSE_CLICK_SECONDARY, 250, 50)
Send("t")
Local $sReturn = ClipGet()
EndFunc
Lets now examine the new code added below. In this iteration, we have added a function to retrieve some web-based information [GetURL()] and store that information in our spreadsheet in column E.
Looking at the function GetURL() you will see that we create a string to use to retrieve web information. Next is the function AutoItSetOption() which is used to modify systems within the language. This option allows us to use a coordinate system based on the current window regardless of its location on the screen. Next, we implement the MouseClick method using the left and right mouse buttons at the coordinates we obtained earlier. This will left click on the address bar, thusly highlighting the URL, and then right click to initiate the context menu. Lastly, the key ‘t’ is sent to issue the Cut option.
The next line closes our IE browser and returns the value stored in the clipboard we obtained by cutting the URL from the IE address bar.
#include <Excel.au3>
#include <IE.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
$i = 0
$sCurrProjID = $aProjData[0][0]
While $sCurrProjID <> ""
Create_TextFile($sCurrProjID, $sCurrDate)
$sURL = GetURL($aProjData[$i][2], $aProjData[$i][3])
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2)
_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
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
Local $oIE = _IECreate("https://www.google.com/maps/search/" & sGPS )
AutoItSetOption("MouseCoordMode", 0)
MouseClick($MOUSE_CLICK_PRIMARY, 250, 50)
MouseClick($MOUSE_CLICK_SECONDARY, 250, 50)
Send("t")
_IEQuit($oIE)
Return ClipGet()
EndFunc
While the above is functional, it should always be a design goal to write efficient code that uses the least amount of calculations. Let’s look at improving the code and introducing a new loop type, the Do … Until Loop.
Do … Until Loop
The Do … Until Loop will loop until a criterion is met. In this example, the loop is continued until $aProjData[$i][0] = “”. This will ensure the loop runs at least 1 time whereas the While … WEnd Loop may not run at all.
To exit loops, other options exist to allow loops to be exited at any point, used in conjunction with IF statements we can create opportunities to exit the function earlier and avoid unneeded calculations and possible errors. On the third line, this system is used to ensure that the first iteration of the loop has applicable data to process if none is found the command ExitLoop skips the rest of the code and continues after the Until … statement.
$i = 0 Do If $aProjData[$i][0] = "" Then ExitLoop Create_TextFile($aProjData[$i][0], $sCurrDate) $sURL = GetURL($aProjData[$i][2], $aProjData[$i][3]) _Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2) _Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2) $i = $i + 1 Until $aProjData[$i][0] = ""
OOP; The Object Model
There are times when MouseClick and MouseMove are useful, however, it is much more reliable to work directly with the program rather than working with the mouse that then works with the program (somewhat blindly). Below we will work with the IE object to move through its data into the current document and lastly to retrieve the URL that is listed in the IE address bar. This makes use of Object Oriented Programming and allows us to interact in a native language with the host application, or to say the application we want to use and automate.
In this example, we will look at what is referred to as the Document Object Model [DOM] for Internet Explorer and briefly look at the simplicity and level of control offered when utilizing the internal methods and properties of Internet Explorer.
It is worth noting that some of the DOM functions are used in the scripting language which adds error control and other functionality to the native functions. One such example is _IENavigate which includes the ability to wait for the www page to complete loading before continuing processing code by default. Using the native function $oIE.Navigate($sURL) does not wait for the page to load before processing the next line of code.
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
Local $oIE = _IECreate()
With $oIE
.Width = 800
.Height = 600
.Visible
.Navigate("https://www.google.com/maps/search/" & sGPS )
.Document.Title = "My Title"
EndWith
Local $sReturn = $oIE.Document.URL
_IEQuit($oIE)
Return $sReturn
EndFunc
Object Utilization; With … EndWith
In the code sample above we can see the keyword With … EndWith used. What this does is takes the object, in this case, $oIE, and uses that object to access the properties and functions contained within the object. In order, using the Internet Explorer Object Model we will set the Width of the Browser Window, The Height, We can set the browser to be visible. Control functions such as Navigate() and set values such as the Title all in shorthand until we issue the EndWith keyword. Outside of the With … EndWith statement the long hand needs to be used as is the case in storing the Document.URL in the code sample above.
Let’s regather our new code before proceeding to the next section;
#include <Excel.au3>
#include <IE.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
$i = 0
Do
If $aProjData[$i][0] = "" Then ExitLoop
Create_TextFile($aProjData[$i][0], $sCurrDate)
$sURL = GetURL($aProjData[$i][2], $aProjData[$i][3])
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
$i = $i + 1
Until $aProjData[$i][0] = ""
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
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
Local $oIE = _IECreate()
With $oIE
.Width = 800
.Height = 600
.Visible
.Navigate("https://www.google.com/maps/search/" & sGPS )
.Document.Title = "My Title"
EndWith
Local $sReturn = $oIE.Document.URL
_IEQuit($oIE)
Return $sReturn
EndFunc
Again, thinking of efficiency and utilizing the least amount of resources, especially time we can find an opportunity here to lessen the number of times we launch Internet Explorer by creating $oIE once and reusing the same object globally, we will also trim code that does not produce data as below;
#include <Excel.au3>
#include <IE.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
Global $oIE = _IECreate()
$i = 0
Do
If $aProjData[$i][0] = "" Then ExitLoop
Create_TextFile($aProjData[$i][0], $sCurrDate)
$sURL = GetURL($aProjData[$i][2], $aProjData[$i][3])
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
$i = $i + 1
Until $aProjData[$i][0] = ""
_IEQuit($oIE)
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
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon$oIE.Navigate("https://www.google.com/maps/search/" & sGPS )
Sleep(1000)
Return $oIE.Document.URL
EndFunc
Slowing things down; Sleep() Function
The changes to the function GetURL greatly reduces the number of instructions used and introduces an important function; Sleep(). The function Sleep() will pause for the number of milliseconds you provide. In this example, we wait 1 second to allow for Internet Explorer to load the pages URL and then return that value.
As stated previously, the least most reliable method of automation is simulating a user. It is much quicker and reliable to interact directly with the program. We have discussed interacting with the Application Object, however, this may not always be accessible. Lets now look at upgrading our Create_TextFile function to work with controls.
Control IDs, Direct Application Interactions
We will again be utilizing the Auto-It Info Tool to identify the controls we need to work with.
Hovering over the area that allows you to enter text in the application notepad.exe shows that the Control ID for this area is named “Edit1”. Having this name allows us to send information and mouseclicks directly to that control.
Let’s modify our code to work with control IDs;
Using ControlSetText() function it is possible to instantly set the data of the control. This is much more reliable than using the standard Send() function as the Send() function does not always manage to correctly send the keys as expected.
Using ControlSend() function it is possible to send text, character by character to the control however much more usefull is the ability to send keyboard shortcuts such as Ctrl-N [“^n”] or Alt-S [“!s”].
Using ControlClick() function it is possible to send mouse clicks directly to controls such as buttons or drop downs.
Func Create_TextFile($sTheID, $sTheDate)
$sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."$sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Completed.txt{ENTER}"
$iProcessID = Run("notepad.exe")
$hWindowHandle = WinWaitActive("Untitled - Notepad")
ControlSetText ( $hWindowHandle, "", "Edit1", $sMessage )
ControlSend ( $hWindowHandle, "", "Edit1", "^s" )
$hSaveAs = WinWait("Save As")
ControlSetText ( $hSaveAs , "", "Edit", $sSaveDir )
ControlClick ( $hSaveAs , "", "[CLASS:Button; INSTANCE:2]" , "left" )
WinClose($hWindowHandle)
EndFunc
While the above code may not reduce our calculation count it is much quicker to work with these elements;
ControlID; the standard ID of the control. A control can be anything
in a windows application.
Class; The standard type of element, examples [ Edit, Button, ComboBox ]
Instance; The index number of the element, example;
[CLASS:Button; INSTANCE:2] refers to the 2nd element of the type button.
Windows Handle; the handle or reference to the window application.
Process ID; the ID Number of the process that the application is using.
Lastly, let’s make one last text file, this one will be different as we will use the URL we obtained from Google Maps to make an Internet Shortcut. As you can see below this can be handled by slightly modifying the Create_TextFile() function into a new unique function as below;
Func Create_WWWLink($sURL)
$sMessage = "[InternetShortcut]" & @CRLF & "URL=" & $sURL$sSaveDir = @MyDocumentsDir & "\Completed\" & $sTheID & "_Map.url{ENTER}"
$iProcessID = Run("notepad.exe")
$hWindowHandle = WinWaitActive("Untitled - Notepad")
ControlSetText ( $hWindowHandle, "", "Edit1", $sMessage )
ControlSend ( $hWindowHandle, "", "Edit1", "^s" )
$hSaveAs = WinWait("Save As")
ControlSetText ( $hSaveAs , "", "Edit", $sSaveDir )
ControlClick ( $hSaveAs , "", "[CLASS:Button; INSTANCE:2]" , "left" )
WinClose($hWindowHandle)
EndFunc
While this may be a workable solution, again simplicity is most sought after. As this is a very similar function to one that we have already created it may be best to modify the one function to work for multiple purposes.
Let’s look at making a more generalized function below;
Func Create_TextFile($sContent, $sFilePath, $sFileName)
$sSaveDir = $sFilePath & $sFileName & "{ENTER}"$iProcessID = Run("notepad.exe")
$hWindowHandle = WinWaitActive("Untitled - Notepad")
ControlSetText ( $hWindowHandle, "", "Edit1", $sContent )
ControlSend ( $hWindowHandle, "", "Edit1", "^s" )
$hSaveAs = WinWait("Save As")
ControlSetText ( $hSaveAs , "", "Edit", $sSaveDir )
ControlClick ( $hSaveAs , "", "[CLASS:Button; INSTANCE:2]" , "left" )
WinClose($hWindowHandle)
EndFunc
Here we have modified the function to write any text to a file and save it as the name and location that is specified. Other methods of controlling and gathering information in regards to windows applications are displayed as well.
Finding clarity through narration; The code comment.
At this point, the code below really becomes a bit difficult to really read through, let’s introduce a concept that saves hours of staring at code waiting for it to reveal its magic. The simple comment. In this language, to enter a comment simply proceed the comment with the; character. It is super good practice to comment your code so that others and even yourself may review the code and quickly see what its function is. Let’s enter some comments below to organize our code.
Finding clarity through separation; The code region.
Another good idea is to use #Region … #EndRegion statements. These allow you to collapse and hide blocks of code so that what you want to work on is more clear. These do not have any effect on the final code and are solely used for organization.
The result of this segment’s code modifications so far are as follows;
#include <Excel.au3>
#include <IE.au3>
#Region Main
;Set global variables
;===========================================================================
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
$sSaveDir = @MyDocumentsDir & "\Completed\"
Global $oIE = _IECreate()
; Program Begin
;===========================================================================
$i = 0
Do
; If no data found in Excel, exit this loop
If $aProjData[$i][0] = "" Then ExitLoop
; Create Proj Completed text file
$sTheID = $aProjData[$i][0]
$sThePath = $sSaveDir & $sTheID & "\"
$sTheFile = $sTheID & "_Completed.txt"
$sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Create_TextFile($sMessage, $sThePath , $sTheFile)
; Create Proj Map url link
$sURL = GetURL($aProjData[$i][2], $aProjData[$i][3])
$sTheFile = $sTheID & "_Map.url"
$sMessage = "[InternetShortcut]" & @CRLF & "URL=" & $sURL
Create_TextFile($sMessage, $sThePath , $sTheFile)
; Store information in Excel
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
; Move to the next Excel record
$i = $i + 1
Until $aProjData[$i][0] = ""
; Exit Internet Explorer
_IEQuit($oIE)
; Program End
#EndRegion Main
#Region Functions
;Function; Create_Textfile
; ReqD (1); $sContent: String representing file contents
; ReqD (2); $sFilePath: String representing save location, end with "\"
; ReqD (3); $sFileName: String representing saved filename and extension
; Sucess: Creates the specified File
; Error: Not Specified
;============================================================================
Func Create_TextFile($sContent, $sFilePath, $sFileName)
$sSaveDir = $sFilePath & $sFileName & "{ENTER}"
; Open Notepad
$iProcessID = Run("notepad.exe")
$hWindowHandle = WinWaitActive("Untitled - Notepad")
; Edit File
ControlSetText ( $hWindowHandle, "", "Edit1", $sContent )
; Save The File
ControlSend ( $hWindowHandle, "", "Edit1", "^s" )
$hSaveAs = WinWait("Save As")
ControlSetText ( $hSaveAs , "", "Edit", $sSaveDir )
ControlClick ( $hSaveAs , "", "[CLASS:Button; INSTANCE:2]" , "left" )
; Exit NotePad
WinClose($hWindowHandle)
EndFunc
;Function; GetURL
; ReqD (1); $sLat: String; a decimal latitude using Northing and Easting
; ReqD (2); $sLon: String; a decimal longitude using Northing and Easting
; Sucess: Returns a string representing the Google Maps URL for the given GPS
; Error: Not Specified
;============================================================================
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
$oIE.Navigate("https://www.google.com/maps/search/" & $sGPS )
Sleep(1000)
Return $oIE.Document.URL
EndFunc
#EndRegion Functions
As you can see above there are now 2 regions in the code. One is for the main program and the other is for the functions of the program. This will allow us to collapse the blocks of code to remove unneeded information from the screen.
Also, it should be noticed that the functions now have multiple lines of comments preceding them. This is used to show what is expected from the function, what variables are needed and what the outcome should be.
Within the functions and main program are commenting as well that again narrate what is happening and what is expected. As in the main program, spaces can also be used to isolate and organize blocks of code.
Calling Functions, Optional Parameters;
Let’s look at one final example of functions and the usage possibilities of our Create_TextFile() function. First, Let’s first define a piece of text to save. For this example, a serialized dataset will be created that can be used within any program that accepts the KML file format such as Google Earth.
You should note that this function has an OPTIONAL argument [$sDesc]. This means you can use this function with either 3 pieces of information OR 4 pieces. A new macro [@CRLF] is in use in this text file to issue a CarriageReturn and LineFeed which will simply start a new line of text. The operators “& _” tell the compiler to continue the line of code on the next line, this is useful for visualizing the layout of multiline text.
;Function; GetKML ; ReqD (1); $sLat: String; a decimal latitude using Northing and Easting ; ReqD (2); $sLon: String; a decimal longitude using Northing and Easting ; ReqD (3); $sProjID: String; Project Identifier for provided GPS ; OptN (A); $sDesc: String; Optional description ; Sucess: Returns a string representing the serialized data for Google Earth ; Error: Not Specified ;============================================================================ Func GetKML($sLat, $sLon, $sProjID , $sDesc = Default) If $sDesc = Default Then $sDesc = "Auto Generated!" $sContent = '' & _
'<?xml version="1.0" encoding="UTF-8"?>' & @CRLF & _
'<kml xmlns="http://www.opengis.net/kml/2.2">' & @CRLF & _
' <Placemark>' & @CRLF & _
' <name>' & $sProjID & '</name>' & @CRLF & _
' <description>Location of project: ' & $sProjID & @CRLF & _
' ' & $sDesc & '</description>' & @CRLF & _
' <Point>' & @CRLF & _
' <coordinates>' & $sLon & ',' & $sLat & ',0</coordinates>' & @CRLF &_
' </Point>' & @CRLF & _
' </Placemark>' & @CRLF & _
'</kml>
' Return $sContent
EndFunc
Adding this to our code we get the resulting code below;
#include <Excel.au3>
#include <IE.au3>
#Region Main
;Set global variables
;===========================================================================
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$aProjData = _Excel_RangeRead ( $oWorkbook, $oWorkbook.Activesheet, "A2:D8")
$sCurrDate = @MDAY & "/" & @MON & "/" & @YEAR
$sSaveDir = @MyDocumentsDir & "\Completed\"
Global $oIE = _IECreate()
; Program Begin
;===========================================================================
$i = 0
Do
; If no data found in Excel, exit this loop
If $aProjData[$i][0] = "" Then ExitLoop
$sTheID = $aProjData[$i][0]
$sLat = $aProjData[$i][2]
$sLon = $aProjData[$i][3]
$sThePath = $sSaveDir & $sTheID & "\"
; Create Proj Map url link
$sTheFile = $sTheID & "_Map.url"
$sMessage = "[InternetShortcut]" & @CRLF & "URL=" & GetURL($sLat, $sLon)
Create_TextFile($sMessage, $sThePath , $sTheFile)
; Create KML for Google Earth
$sTheFile = $sTheID & "_GEarth.kml"
$sMessage = GetKML($sLat, $sLon, $sTheID)
Create_TextFile($sMessage, $sThePath , $sTheFile)
; Create Proj Completed text file
$sTheFile = $sTheID & "_Completed.txt"
$sMessage = "Project " & $sTheID & " Completed: " & $sTheDate & "."
Create_TextFile($sMessage, $sThePath , $sTheFile)
; Store information in Excel
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sURL, "E" & $i+2)
_Excel_RangeWrite( $oWorkbook, $oWorkbook.Activesheet, $sCurrDate, "B" & $i+2)
; Move to the next Excel record
$i = $i + 1
Until $aProjData[$i][0] = ""
; Exit Internet Explorer
_IEQuit($oIE)
; Program End
#EndRegion Main
#Region Functions
;Function; Create_Textfile
; ReqD (1); $sContent: String representing file contents
; ReqD (2); $sFilePath: String representing save location, end with "\"
; ReqD (3); $sFileName: String representing saved filename and extension
; Sucess: Creates the specified File
; Error: Not Specified
;============================================================================
Func Create_TextFile($sContent, $sFilePath, $sFileName)
$sSaveDir = $sFilePath & $sFileName & "{ENTER}"
; Open Notepad
$iProcessID = Run("notepad.exe")
$hWindowHandle = WinWaitActive("Untitled - Notepad")
; Edit File
ControlSetText ( $hWindowHandle, "", "Edit1", $sContent )
; Save The File
ControlSend ( $hWindowHandle, "", "Edit1", "^s" )
$hSaveAs = WinWait("Save As")
ControlSetText ( $hSaveAs , "", "Edit", $sSaveDir )
ControlClick ( $hSaveAs , "", "[CLASS:Button; INSTANCE:2]" , "left" )
; Exit NotePad
WinClose($hWindowHandle)
EndFunc
;Function; GetURL
; ReqD (1); $sLat: String; a decimal latitude using Northing and Easting
; ReqD (2); $sLon: String; a decimal longitude using Northing and Easting
; Sucess: Returns a string representing the Google Maps URL for the given GPS
; Error: Not Specified
;============================================================================
Func GetURL($sLat $sLon)
Local $sGPS = $sLat & "," & $sLon
$oIE.Navigate("https://www.google.com/maps/search/" & $sGPS )
Sleep(1000)
Return $oIE.Document.URL
EndFunc
;Function; GetKML
; ReqD (1); $sLat: String; a decimal latitude using Northing and Easting
; ReqD (2); $sLon: String; a decimal longitude using Northing and Easting
; ReqD (3); $sProjID: String; Project Identifier for provided GPS
; OptN (A); $sDesc: String; Optional description
; Sucess: Returns a string representing the serialized data for Google Earth
; Error: Not Specified
;============================================================================
Func GetKML($sLat, $sLon, $sProjID , $sDesc = Default)
If $sDesc = Default Then $sDesc = "Auto Generated!"
$sContent = '' & _
'<?xml version="1.0" encoding="UTF-8"?>' & @CRLF & _
'<kml xmlns="http://www.opengis.net/kml/2.2">' & @CRLF & _
' <Placemark>' & @CRLF & _
' <name>' & $sProjID & '</name>' & @CRLF & _
' <description>Location of project: ' & $sProjID & @CRLF & _
' ' & $sDesc & '</description>' & @CRLF & _
' <Point>' & @CRLF & _
' <coordinates>' & $sLon & ',' & $sLat & ',0</coordinates>' & @CRLF &_
' </Point>' & @CRLF & _
' </Placemark>' & @CRLF & _
'</kml>'
Return $sContent
EndFunc
#EndRegion Functions
We now have a script that will:
1) Read many entries from Excel, processing only the good entries.
2) Request and retrieve information from a web-based resource
3) Create an internet shortcut to our retrieved information
4) Create a file to document the finalization of a project.
5) Create a KML file to showcase project location and information
6) Writes multiple pieces of information back into Excel
Topics Covered [but not limited to];
A-1) Variables; 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) Repetitions and Loops
B-4) While ... WEnd Loop
B-5) Adding to Arrays, Setting and retrieving array data
B-6) For ... Next Loop
B-7) Read and Write in Excel
B-8) If ... END statement
C-1) AutoIt Window Info Tool
C-2) Do ... Until Loop
C-3) Internet Explorer Document Object Model [DOM]
C-4) With ... EndWith Object Utilization
C-5) Sleep() function for delays
C-6) Control IDs, Manipulating Application Controls
C-7) Code Clarity through Commenting (;) and #Region ... #EndRegion
C-8) Creating and Using Functions with Optional Parameters