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:
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"
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."
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"
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for