Mr. Script

Automate This! Part 4: You Send Me

In the final installment of this four-part series, Chris shows that the best solution may not be the most elegant one.

• By Chris Brooke
• 12/01/2005

To relate this to our case-study application, this task requires that we connect to the Web site of our shipping company and schedule pickup of an order, which will then be shipped to the customer. The process is straightforward: connect; wait; send keys; wait; wait; wait. Repeat. There's quite a bit of manual configuration when you first set up the site, but when dealing with a Web site used for one specific function, it’s worth the time to script the interaction. Then you need only enter the relevant information in your front-end application (which, in our case, is the order ID), and the rest gets handled automatically.

Note: A word of warning: Hard-coding keystrokes for Web page interaction is a process fraught with potential pitfalls. At a minimum, you want to code your “wait for response” time to be much longer than what you experience when setting things up. We’ve all been on the receiving end of a slow Web server. If you don’t code the script to wait long enough, you’ll find it failing more often than not. The good news is that you open Internet Explorer interactively, so you can watch as the script executes. This helps you time everything properly.

Our case study application uses DHL for shipping, so we’ll use it as a specific example. Then we’ll review the relevant commands so you can tailor it to the shipping company of your choice.

Sub ShipCD
Call DisableUI

'Browse IE to DHL
appExplorer = Shell("c:\program files\internet explorer\iexplore.exe “ _
& “https://sso.dhl-usa.com/sso/login_main.asp?hdnSiteID=1&nav=Login", 3)
DoEvents
WaitSeconds (20)

'Enter Username and password
SendKeys "username", True
WaitSeconds (3)
SendTabs (1)
SendKeys "password{ENTER}", True
WaitSeconds (35)

'Skip to the receiver's name field
SendTabs (6)

'Enter the Data
SendKeys strName, True
SendTabs (1)
SendKeys strCompany, True
SendTabs (2) ' Skip Suite#
SendKeys strAddress1, True
SendTabs (1)
SendKeys strAddress2, True
SendTabs (1)
SendKeys strCity, True
SendTabs (1)

‘The following is a call to a sub that simply
‘arrows down to the appropriate state
Call ProcessDHLState(strState)
SendTabs (1)
SendKeys strPostCode, True
SendTabs (1)
SendKeys strPhone, True
SendTabs (1)
SendKeys tEmail, True
SendTabs (6)
SendKeys "D", True 'DHL Express Envelope
SendTabs (1)
SendKeys "Product shipment for OrderID: " + tOrderid 'Description
SendTabs (1)
SendKeys "CSAuto", True 'Reference
SendTabs (1)
SendKeys "{DOWN}", True
SendTabs (1)
SendKeys "I I I", True
SendTabs (7)
SendKeys " ", True
SendTabs (4)
If Val(strService) = 0 Then
'2nd Day
SendKeys "2", True
Else
'Overnight
SendKeys "N N N", True
End If
SendTabs (2)
SendKeys "{DOWN}", True
SendKeys "{UP}", True
SendTabs (8)

'This will actually cause the Shipment to print.
SendKeys "{ENTER}", True

'Wait for the printout Screen to come up
WaitSeconds (25)

'Make IE Print it!
SendKeys "%F", True
WaitSeconds (3)
SendKeys "P", True
WaitSeconds (6)
SendKeys "{ENTER}", True
WaitSeconds (15)

'Close IE
SendKeys "%{F4}", True
DoEvents
WaitSeconds (3)

Call EnableUI

bProcessing=False
Call StartTimer
End Sub

Public Sub WaitSeconds(iSeconds As Integer)
DoEvents
Application.Wait Now + TimeValue("00:00:" + Right$("00" + Trim(Str$(iSeconds)), 2))
DoEvents
End Sub

Besides the obvious SendKeys and SendTabs commands, we use two other methods: WaitSeconds, a user-defined sub that simply suspends execution for a prescribed number of seconds (for instance, to wait for a page to load); and DoEvents, a Windows function that causes the application to check its queue. We use it here to allow us to cancel the process and close Internet Explorer prior to completing the shipping request.

After sending the keys and scheduling the shipment, we re-enable the GUI, set our semaphore—the Boolean variable bProcessing—to False (so that we’ll start checking for queued items again), restart the timer, and exit back to “home state”. What could be easier? (Okay, Okay… forget I asked. It was supposed to be rhetorical.)

Saving Time and Money
Configuring the correct sequence of SendKeys for your shipping provider’s Web site is a simple matter of loading the page, tabbing through to the appropriate fields (keeping track of the tab count as you do so), filling in the fields with the correct data, and tabbing to the button and hitting Enter. As I said, this will require a bit of manual processing at first. The good news is that after tweaking it to your satisfaction, you will save time (and money) by not requiring that someone sit at the “shipping computer” copying information from the order screen and entering it on the shipping provider’s Web page.

The Right Tool
We’ve covered a lot of ground over the last few months. It is my fervent wish that I have given you a good foundation to build on when attempting to integrate disparate systems. But if I’ve shown you anything at all, I hope I have shown you how important it is to select the right technique for the task at hand. The method you use doesn’t have to be pretty, just as long as it works!