Mr. Script

Automate This! Part 3

Do that thing you do.

• By Chris Brooke
• 11/01/2005

Last month, we used the Timer functionality of the User32 library to cause our application to essentially wait for something to do - the timer simply checked the queue every 10 seconds until it encountered a job that needed processing. This month, we'll go through the process of executing a job. Once the operations are complete, we simply return to our waiting state.

Remember that our overall goal is to integrate a Web-based order system with electronic software delivery and CD burning/shipping (when required).

Stop Right There!
The first thing we need to do when we encounter a queued job is to stop the timer. Now, in theory we shouldn't have to. After all, the timer function checks the global Boolean variable bProcessing each time it fires. However, as I mentioned when we began this odyssey a few months ago, things rarely work in the real world as they do in theory. So let's just be safe and disable the timer completely, shall we?

Sub EndTimer()
If TimerID = 0 Then Exit Sub
On Error Resume Next
KillTimer 0&, TimerID
TimerID = 0
End Sub

Note: KillTimer is a function of the User32 library (just like SetTimer). Please refer to the Declare Function statement in last month's column to view the syntax.

Next we parse the job file, which contains at least one, but perhaps several tasks. A task common to almost every job is uploading a file to the FTP server for the customer to download. The parsing logic from our case study application is irrelevant. For your application you can use whatever file format and parsing code you wish. In the interest of brevity, let's assume that we've parsed the job and determined that we need to upload a file. This will allow us to get right to the good stuff: uploading a file using wininet.dll.

Like the User32 library, WinInet is a Windows library that we cannot access directly using VBScript. From our Excel VBA app, however, it's a piece of cake. Now, we could simply shell out to the command prompt and use the DOS-based FTP client to send the file, but using WinInet is somewhat more elegant.

Let's refresh our memories by looking at the WinInet functions we will be using:

Private Declare Function InternetOpen _
Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String , _
ByVal lAccessType As Long , _
ByVal sProxyName As String , _
ByVal sProxyBypass As String , _
ByVal lFlags As Long ) _
As Long

Private Declare Function InternetConnect _
Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal hInternetSession As Long , _
ByVal sServerName As String , _
ByVal nServerPort As Integer , _
ByVal sUsername As String , _
ByVal sPassword As String , _
ByVal lService As Long , _
ByVal lFlags As Long , _
ByVal lContext As Long ) _
As Long

Private Declare Function InternetCloseHandle _
Lib "wininet.dll" (ByVal hInet As Long ) _
As Integer

Private Declare Function FtpPutFile _
Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long , _
ByVal lpszLocalFile As String , _
ByVal lpszRemoteFile As String , _
ByVal dwFlags As Long , _
ByVal dwContext As Long ) _
As Boolean

Private Declare Function FtpDeleteFile _
Lib "wininet.dll" Alias "FtpDeleteFileA" _
(ByVal hFtpSession As Long , _
ByVal lpszFileName As String ) _
As Boolean

Private Declare Function FtpSetCurrentDirectory _
Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long , _
ByVal lpszFileName As String ) _
As Boolean

Private Declare Function FtpCreateDirectory
Lib "wininet.dll" Alias "FtpCreateDirectoryA"
(ByVal hFtpSession As Long ,
ByVal lpszFileName As String )
As Boolean

You must put the above code at the beginning of your module, just as we did with the SetTimer and KillTimer functions. This tells Windows how to reference the library. Now, rather than explain every parameter of each function, I'll explain the really relevant ones as we call them.

All that remains now is to execute the file upload functionality. If you refer back to our UI design from last month, you'll note that we have a button on our form to "Create an FTP link for order." This allows the user to manually initiate this process. Since we want to initiate it in code as a result of a queued job, all we need to do is simulate clicking the button.

Call CreateFTPLink.CommandButton2_Click

This causes the code for that event to execute.

Public Sub CommandButton2_Click()
Dim bTemp As Boolean
Dim lTemp As Long

Dim strTmpLocal As String
Dim strTmpRemote As String
Dim strTmpFile As String

'Disable the user interface and stop the timer
Call DisableUI

strTmpLocal = "c:\FileToUpload\MyFile.zip"
strTmpRemote = "\pub\downloads\MyUploadedFiles"
strTmpFile = "MyFile.zip"

'We've got our file info, now call FTPSend
lTemp = 0
lTemp = _
FTPSend(strTmpLocal, strTmpRemote, strTmpFile)

If lTemp <> 0 Then
'FTPSend failed.
'Put code here to deal with that.
Exit Sub
End If
'Upload succeeded
'Enable our UI buttons and restart the timer
Call EnableUI
End Sub

The above Sub in turn calls the FTPSend Function, which is what uses the WinInet library to actually upload the file.

Public Function FTPSend _
(strLocalPathFile As String , _
strRemotePath As String , _
strRemoteFile As String ) As Long

Dim lngInet As Long
Dim lngINetConn As Long
Dim blnRC As Boolean
Dim lFailed As Long

'Clear out our variables
lngInet = 0
lngINetConn = 0
blnRC = False
lFailed = 0

'Open an internet session
lngInet = InternetOpen _
("AutoFTP", 1, vbNullString, vbNullString, 0)
'"AutoFTP" is the argument we use to refer
'to the connection. '1' means we are creating
'an FTP connection

If lngInet = 0 Then
'internet open failed
lFailed = 1
GoTo CloseConnections
End If

'Connect to FTP site
lngINetConn = InternetConnect _
(lngInet, "ftp.myserver.com", _
0, "username", "password", 1, 0, 0)
'The above arguments should be apparent:
'Server name; Username; Password, etc.

If lngINetConn = 0 Then
'internet ftp connect failed
lFailed = 2
GoTo CloseConnections
End If

'Delete the remote file if it exists
blnRC = FtpDeleteFile _
(lngINetConn, strRemotePath + "\" + strRemoteFile)

'Create remote dir if it doesn't exist
blnRC = FtpCreateDirectory _
(lngINetConn, strRemotePath)

'Set this to the remote working directory
blnRC = FtpSetCurrentDirectory _
(lngINetConn, strRemotePath)

'Send the file
blnRC = FtpPutFile _
(lngINetConn, strLocalPathFile, strRemotePath + _
"\" + strRemoteFile, 2, 0)

If blnRC = False Then
'Failed
lFailed = 3
GoTo CloseConnections
End If

CloseConnections:
If lngINetConn <> 0 Then InternetCloseHandle lngINetConn
If lngInet <> 0 Then InternetCloseHandle lngInet
FTPSend = lFailed
End Function

You may notice that I reference DisableUI and EnableUI, but don't include the code. In the interest of space, I left them out. Suffice to say that they simply disable or enable the controls on the form and then call EndTimer and StartTimer respectively.

The rest of the code is pretty straightforward. I "hard-wired" the paths and filenames, but they could just as easily be passed as arguments to the Sub/Function. Also, note that if the FTPSend function fails, it returns a value based on where it failed. A '1' means that it failed to create the initial WinInet object; '2' means that it failed to connect to the site; '3' means that it failed to upload the file. Since this value is returned to the calling routine as lTemp, we can add error-handling code to respond appropriately.

So Much For Elegant
WinInet has much more functionality than just uploading files to an FTP server. I recommend that you take a look at some of its other capabilities. You can browse its object model on MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wininet/wininet/wininet_reference.asp). Unfortunately, there's not always a component or Windows library to provide an elegant interface to a given task. Next month, we're going to look at the other end of the spectrum: shelling out to the command prompt and using SendKeys to navigate Web pages. It's ugly, but just as functional.

Make sure you have your boots on — you're going to get dirty.