VanDyke Software

Index

Automating Uploads with VCP and Outlook

This tip was submitted to us by a customer who is using VCP, a command-line SFTP file transfer utility that ships with VShell® server and SecureCRT®, to perform automated uploads in conjunction with Microsoft Outlook. Each day he was manually selecting files and adding the date to the filenames. Now he simply clicks on a custom button in Outlook and his files are renamed with the date and sent securely using VCP.

Jim is a true remote worker who hasn't been to the office since 1993. He works remotely from his home office for a Commodity Trading Advisor firm with offices in New York and Chicago. "Commodity trading tends to be 24-7 so it's a natural for working from home," he said. Jim wears many hats. He is the senior IT manager and programmer. In addition to remote administration, he oversees all operations and acts as the firm's primary analyst. Working efficiently is very important to him.

Jim has a variety of Linux servers and Windows boxes at his home office. For remote administration and file transfer, and to tunnel applications like email, he uses VShell server and SecureCRT. This lets him work on both Windows and Linux boxes and administer a Windows/Linux cluster in New York.

Every day, Jim analyzes price data and creates Excel spreadsheets with commodity buy and sell orders. These files are then transferred to the Chicago and New York offices via email.

He wanted to streamline the file transfer process and secure the data in transit. It was a simple step to automate and secure this transfer using the VCP command-line SFTP utility available with VShell.

Jim decided to use Outlook since he was already using it for email and calendars and it made sense to do the transfer from a program that was right there and active all day. To do this, he created a script for VCP using VBA (see below). He now has a customized "send orders" button on the Outlook toolbar. Each day, he simply clicks the button, and VCP connects to the remote server, sends the Excel files that have been created, and disconnects when the process is complete.

Combining VCP with Outlook has saved him a lot of time. Sending the daily files is a one-click operation and he no longer has to manually select the files and add the date.

So far, Jim hasn't had any problems. He does note that there is no error handling in this function. "Since I do all the programming locally, so to speak, in the event something goes wrong, I'll just check it out in debug mode."

Option Explicit

Sub UploadFilesToSFTPServer()
    Dim szExecution As String
    Dim RetVal
    Const szSourcePath As String = "E:\XL7DOCS\XYZ\POSITION\"
    Dim szDate As String

    'Determine the file name by using today's date (MMDD)
    szDate = NN(Month(Now)) & NN(Day(Now))
    szExecution = "VCP -i pkIDfile " & szSOURCE_PATH & "OR" & szDate & _
         ".zip username@ipaddress:C:\TEMP"

    RetVal = Shell(szExecution, vbNormalFocus)

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function NN(nNumber)
' Normalizes single digit numbers to have a 0 in front of them
      If nNumber < 10 Then
            NN = "0" & nNumber
      Else
            NN = nNumber
      End If
End Function