Learn HP Quick Test Professional Online

Recently I was faced with the task of developing an automated script to compare some reports in Excel. Well I do not want to go into the file comparison part today but I would like to mention the part in my automation where I had to ftp a file from my local machine to a ftp server and download a baseline file from the server to my local machine. My first instinct to automate the part of ftp-ing a file was to use the SystemUtil.Run "cmd" statement and then use a series of Window("ftp").Type commands to execute the statements and put all this in a function. But somehow I didnt like the look of the program. Initially I thought this was OK but then later on I realised that whenever I needed another script to call this function, I even had to have the Object Repository loaded as the Window("ftp") object needed to be present in the calling script. This was not an ideal solution. Thats when I thought that I had to do it without the usage of any objects. OK....so how do we accomplish this.
The command "ftp" has an option "-s" where we input a file which contains a set of ftp commands. So with just one ftp command, we would be able to execute a series of commands. Let me try to make it more clear with a program..

'#################################################################

Option Explicit
Public Function FTPDownload(serverName, directoryPath, fileName)

Const userName = "ftpuser"
Const password = "ftppassword"
Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2

Dim fso, fsh
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsh = CreateObject("WScript.Shell") 'the fsh object will run the command
directoryPath = Trim(directoryPath)
fileName = Trim(fileName)

Dim fScript, fTemp, fTempFile, fResult
'build a script file to store all the ftp commands as we will be executing the script file
fScript = fScript & "USER " & userName & vbCRLF
fScript = fScript & password & vbCRLF
fScript = fScript & "lcd " &directoryPath & vbCRLF
fScript = fScript & "cd /etc" & vbCRLF
fScript = fScript & "binary" & vbCRLF
fScript = fScript & "prompt n" & vbCRLF
fScript = fScript & "mget " & Chr(34) & fileName & Chr(34) & vbCRLF
fScript = fScript & "quit" & vbCRLF & "quit" & vbCRLF & "quit" & vbCRLF
fTemp = fsh.ExpandEnvironmentStrings("%TEMP%")
fTempFile = fTemp & "\" & fso.GetTempName 'returns a random name
fResult = fTemp & "\" & fso.GetTempName

Dim fFTPScript
'Write the input script file for the ftp command to a temporary file.
Set fFTPScript = fso.CreateTextFile(fTempFile, True)
fFTPScript.WriteLine(fScript)
fFTPScript.Close
Set fFTPScript = Nothing
fsh.Run "%comspec% /c FTP -n -s:" & fTempFile & " " & serverName & _ " > " & fResult, 0, TRUE

Dim fFTPResults, sResults
'Check results of transfer.
Set fFTPResults = fso.OpenTextFile(fResult, ForReading, _ FailIfNotExist, OpenAsDefault) sResults = fFTPResults.ReadAll
fFTPResults.Close fso.DeleteFile(fTempFile)
fso.DeleteFile (fResult)
If InStr(sResults, "226 Transfer complete.") > 0 Then 'checks success of transfer
FTPDownload = True 'Function returning True if transfer is successful
ElseIf InStr(sResults, "No file") > 0 Then
FTPDownload = "Error: File Not Found"
ElseIf InStr(sResults, "cannot log in.") > 0 Then
FTPDownload = "Error: Login Failed."
Else FTPDownload = "Error: Unknown."
End If
Set fso = Nothing
Set fsh = Nothing
End Function

Let me explain the script now.... I have created a function here as you can call this function whenever you want to use it.

The first parameter is the name of the server like ftpservername.companyname.com.

The second parameter is the directory path where the file to be downloaded needs to be placed locally. In this example we are trying to copy a file passed through the parameter "fileName" under the /etc directory. Now...what is vbCRLF ? This is a Carriage return-Line Feed conbination. In simple words, its equivalent to typing an "Enter" on the keyboard.

You can see that while using the "mget" command, I am using Chr(34). Chr(34) is the equivalent of quotes("). If your filename contains spaces, you would need to include the filename in quotes. Well...why didnt we just use the " then ? Thats because if you use " instead of Chr(34), the VB interpreter will think that you are going to have one more quote and it would take whatever in between as a string constant and this will result in a syntax error.

Also we are putting the script file and the log file in a temporary location. The ftp file transfer is successful when we see the message "226 Transfer complete". Thats why we are using the Instr() function.

Then we use fsh.Run to run the ftp command and pass the script as an input.

Finally set the objects to "Nothing" to avoid memory leaks.

Well to upload your file, just use "mput" instead of "mget"

You would also have observed the usage of Option Explicit on the top. Its a best practice to always have this statement at the beginning of all your scripts so that you dont make a mistake of mistyping a variable name.

So go ahead with your ftp function and tune it to your needs...

Your friend in need,