Excel VBA – FTP Transfer – Files Download Upload

Secure FTP Upload Thru Different Methods

FTP automation is done to transfer data from PC or web server to a FTP file hosting service.

Often this data is served as offsite backups that can be restored back when there is any loss in live data or a data corruption occurs.

In such case, programmers do automation of FTP upload to minimize manual work load. In this article, we will see how to do this through some basic command.

1. Command Line: Secure FTP upload to Server & Local Computer

In Windows Operating System, it is possible to do simple file transfer using FTP utility from Command Prompt (CMD) itself.

• Press Windows + R Keys -> CMD -> FTP -> Help. This will show you all the commands available in FTP utility.

Above command prompt option will be useful when there are only few files to be transferred or archived. If the list is huge, then we need a more flexible FTP client or we can do it with a Excel Macro.

We will be able to create a compact, customization FTP Uploading Site or Download files to FTP server with the code in this blog.

The code in this article can be used to automate most online data backup needs.

2. VBA FTP Upload with INET

With this method, we will be using INET option to transfer files with VBA Code. If you are using Windows versin above 7, then the below code might not work for you.

Initial Setup for file VBA FTP Upload

1. Download the component MSINET.OCX (from this link – non official – use it on own risk).
2. From Excel VBA Project Editor (alt F11 from Excel sheet), Go to Menu -> Tools -> References -> Browse & choose the downloaded OCX file.
3. Then, from the same popup, choose ‘Microsoft Internet Transfer Controls 6.0′ from list & click ok.

Now, you have included INET to your VBA project. If your computer already has VB6, then you dont have to download OCX file, since it will be already present.

Excel VBA FTP Upload – Code Execution

It is assumed that you have list of files to be processed is in your worksheet. Issue a DIR or LS command to server from FTP Prompt, get the File Name List to Excel and then proceed with this VBA FTP Upload.

Remember to change the below details before executing the code.

• FTP server URL or IP address,
• User Name & Password,
• Remote File Name and
• Local File Name

Sub Get_File_From_FTP()
    'Variable Declarion Section
    Dim FTP As Inet
    
    'Assign Host URL, Source and Destination File path
    HostURL = ThisWorkbook.Sheets(1).Cells(1, 1)
    FileSource = ThisWorkbook.Sheets(1).Cells(1, 2)
    FileDestination = ThisWorkbook.Sheets(1).Cells(2, 2)
    
    'Create New instance of Object and Assign the Parameters
    Set FTP = New Inet
    With FTP
        .URL = HostURL
        .Protocol = icFTP
        'Replace with your Login and Password Below. Many FTP servers allow Anonymous access with below credentials
        .UserName = "anonymous"
        .Password = "mail@gmail.com"
        .AccessType = icUseDefault
        'Use "Get" to Download and "Put" Option to Upload File to FTP Server
        .Execute .URL, "Get " & FileSource & " " & FileDestination
    End With
    Do While FTP.StillExecuting
        DoEvents
    Loop
    
    'Status of FTP through Voice Message
    Application.Speech.Speak "Process Completed;" & FTP.ResponseInfo
    
    If FTP.ResponseCode = 0 Then
        'SomeTime FTP steps will execute successfully, but file will not be there in Destination path
        'Search for the file in the Path mentioned in this Message Box
        MsgBox "File is Copied to :" & VBA.CurDir
    End If
    Set FTP = Nothing
End Sub

Note:

• Make sure that you have proper FTP site access to read and write files to the Server and Destination Path.
• Sometimes, File will be placed in System Current Working directory though you mention a destination Path. After code execution, verify whether the file is in the path provided by command “VBA.CurDir”
• Instead of ‘Get’ Command, a ‘Put’ Command will upload file to remote FTP server.

With this, initial setup is completed, proceed to the code. Since we are executing this code from Excel, we can customize and automate this process to transfer or backup even a big list of files to FTP server.

3. Traditional Batch Mode When VBA upload Fails?

The INET option has very few parameters and does not have debug options. Most times, you will end up in trying different options suggested in various discussion forums. If nothing works out, then follow this method.

1. Create a Text file with commands to FTP Server, as in below given sample. Save this file in your system (For example D:\Command_File.txt).
2. Then, from Command Prompt, use the command ‘FTP -i -s:D:\Command_File.txt‘.
3. Every command in the file will be execute one by one and the files will be transferred one by one.

open ftp.server.url.edu
anonymous
cd pub
lcd D:\TestFolder
mget *
quit

This is how we can do FTP File transfer using VBA or Traditional Batch command mode for any of Data backup services or data transfer Automation Purpose.

More Tips: Download HTML table from Webpage to Excel