How to Execute FTP Commands With VBA

Written by jonah quant
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Execute FTP Commands With VBA
Have your VBA programs upload and download data from FTP servers. (Dynamic Graphics/Dynamic Graphics Group/Getty Images)

Visual Basic for Applications, or VBA, is a version of Microsoft's Visual Basic language, integrated into the Office suite of productivity software tools. VBA programs run from within an Office tool -- for example, Word -- accessing and manipulating the data currently managed by that tool -- for example, a Word document. A VBA program can read its input from, or write its results to, files hosted on a remote server over the file transfer protocol, or FTP. You can write VBA code that instructs FTP client software -- preinstalled by default on your computer -- to interact with FTP servers.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Create the FTP command file with a list of commands that the FTP client will execute. The following VBA code fragment creates such a file:

    Dim ftpFileHandle As Integer

    ftpFileHandle = FreeFile

    Open "ftpCommand.txt" For Output As #ftpFileHandle

    Print #ftpFileHandle, "open ftpserver.com"

    Print #ftpFileHandle, "userid"

    Print #ftpFileHandle, "password"

    Print #ftpFileHandle, "send localfile.xls"

    Print #ftpFileHandle, "recv remotefile.doc"

    Print #ftpFileHandle, "bye"

    Close #ftpFileHandle

    Replace "ftpserver.com" with the name of the FTP server, "userid" with the name of your account in that server and "password" with your password. The sample code uploads a spreadsheet contained in a file named "localfile.xls" and downloads a document contained in a file named "remotefile.doc." You can use any of the commands from the "List of FTP Commands" on the NSF Tools website before closing the session with "bye."

  2. 2

    Create a batch, or executable script, file that invokes the FTP client to have it execute the FTP command file. The following VBA code fragment creates such a file:

    Dim batFileHandle As Integer

    batFileHandle = FreeFile

    Open "doFtp.bat" For Output As #batFileHandle

    Print #batFileHandle, "ftp -s:ftpCommand.txt"

    Close #batFileHandle

  3. 3

    Execute the batch file by including the following line in your VBA code:

    Shell ("doFtp.bat"), vbHide '', vbMinimizedNoFocus

    Note that the line contains two single quotes, without any character between them, immediately before the second comma. Your VBA program will invoke the batch file that, in turn, will invoke the FTP client and have it execute the sequence of commands from Step 1.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.