(13 votes, average: 3.92 out of 5)

Today we will learn how to download and upload files using a VBA FTP script. FTP is a great way to keep your Excel files connected to your file servers, back up your files and worksheets automatically or simply download/upload document without any additional manual hassle.

Let us start with learning how to Download Files from FTP using VBA FTP. The below code defines the procedure FtpDownload which does just that:

Syntax

strRemoteFile
A string path to the file on the remote FTP drive which you want to download e.g. “//home/user/text file.txt”

strLocalFile
A string path to the file on the local drive which you want to save the remote file to e.g. “C:\text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

VBA FTP Upload Files

Now let us move to learning how to Upload Files from your local drive to FTP using VBA FTP. The below code defines the procedure FtpUpload which does just that:

Syntax

strLocalFile
A string path to the file on the local drive which you want to upload e.g. “C:\text file.txt”
strRemoteFile
A string path with the name of the upload file on the remote drive to e.g. “//home/user/text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

Summary and alternatives

Downloading and Uploading files to an FTP server is a very useful task. Although there are several things to remember…

Do not keep your password directly in your Workbook. Your password should be stored in a secure way and there is always a chance you might share your Excel Workbook without deleting the embedded password. A good tip is to save the password in a separate text file on your local drive to which no one else has access. See Reading Files in VBA to do this.

Do you use FTP for file versioning? Use SVN or a dedicated solution instead! Although saving and versioning files to FTP is one way to go don’t rely on custom basic solution to long known problems. Setup SVN on your server of use a dedicated service like OneDrive, DropBox of Google Drive.

Do you want to download files from web servers / pages not from FTP? See this post on Downloading Files using VBA.

(7 votes, average: 4.71 out of 5)

Looking to download / upload files from FTP with VBA? Read my blog post on VBA FTP

Excel can be a great tool to harness the data of the Internet. If you are more into the subject of Web Scraping I encourage you to take a look at the Excel Scrape HTML Add-In which let’s you easily download HTML content from most Web Pages without resorting to VBA. In case browser simulation is needed read on my Simple class for using IE automation in VBA.

In some cases you will need to download large files (not text/HTML) and will want to be able to control the process of downloading the data e.g. might want to interrupt the process, enable the user to interact with Excel (DoEvent) etc. In these cases the above procedure won’t do. The procedure below may however prove more efficient as it will download the file in 128 byte chunks of data instead of a single stream.

Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
Private Declare Function InternetReadBinaryFile Lib "wininet.dll" Alias "InternetReadFile" (ByVal hfile As Long, ByRef bytearray_firstelement As Byte, ByVal lNumBytesToRead As Long, ByRef lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lHeadersLength 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

Sub DownloadFile(sUrl As String, filePath As String, Optional overWriteFile As Boolean)
Dim hInternet, hSession, lngDataReturned As Long, sBuffer() As Byte, totalRead As Long
Const bufSize = 128
ReDim sBuffer(bufSize)
hSession = InternetOpen("", 0, vbNullString, vbNullString, 0)
If hSession Then hInternet = InternetOpenUrl(hSession, sUrl, vbNullString, 0, INTERNET_FLAG_NO_CACHE_WRITE, 0)
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1

If hInternet Then
iReadFileResult = InternetReadBinaryFile(hInternet, sBuffer(0), UBound(sBuffer) - LBound(sBuffer), lngDataReturned)
ReDim Preserve sBuffer(lngDataReturned - 1)
oStream.Write sBuffer
ReDim sBuffer(bufSize)
DoEvents

Do While lngDataReturned <> 0
iReadFileResult = InternetReadBinaryFile(hInternet, sBuffer(0), UBound(sBuffer) - LBound(sBuffer), lngDataReturned)
If lngDataReturned = 0 Then Exit Do

ReDim Preserve sBuffer(lngDataReturned - 1)
oStream.Write sBuffer
ReDim sBuffer(bufSize)
DoEvents
Loop

oStream.SaveToFile filePath, IIf(overWriteFile, 2, 1)
oStream.Close
End If
Call InternetCloseHandle(hInternet)
End Sub


See effect below when executing macro:

How it works

The procedure will download the binary file in 128 byte chunks while saving the contents to the data stream and flushing it into the file once completed. In between the chunks you can call “DoEvents” to enable user interaction, inform the user of the download progress “Application.StatusBar” or do other thing including interrupting the process and closing the connection. In case you want to do a proper Progress Bar and inform the user of the % progress you may want to leverage this solution.

Example

Let us use the procedure above to download a simple text file from AnalystCave.com:

Sub TestDownload()