How to Copy Files From FTP Location Using VBA - Step by Step Guide

Suppose you want to download files from a single or multiple ftp directory to local folder, then this code will help you. You can download all available files or particular files from ftp folder.

FTP Server Address: 203.55.32.44

FTP Folder Location: "/HOME/Data_Files/" and "/HOME/Data_New/"

Local Folder Location: C:\Source Folder\FTP Files\

Step 1: Open Excel and Press key Alt+F11. It will open Microsoft Visual Basic for Applications Window.

Step 2: Go to Insert >> Module menu.

Step 3: Copy below code and paste in the code window:

Sub Download_ftp_files()

'****************** Section 1 ******************

Dim fso, ofso, MySite

Set fso = CreateObject("Scripting.FileSystemObject")

Set ofso = CreateObject("Scripting.FileSystemObject")

Application.ScreenUpdating = False

Application.DisplayAlerts = False

'****************** Section 2 ******************

'This script is created to download files from FTP servers for CuteFTPPro window application.

'Create FTEConnection object

Set MySite = CreateObject("CuteFTPPro.TEConnection")

'Connect FTP Server

'It will connect remote server host name, protocol, port, etc.

MySite.Host = "254.55.32.44"

MySite.Protocol = "FTPS_IMPLICIT"

MySite.Port = 990

MySite.Retries = 30

MySite.Delay = 30

MySite.MaxConnections = 1

MySite.TransferType = "AUTO"

MySite.DataChannel = "DEFAULT"

MySite.ClearCommandChannel = False

MySite.ClearDataConnection = False

MySite.AutoRename = "OFF"

'****************** Section 3 ******************

'Enter Username and Password – Three different ways.

'1^st is below where it will pop-up to enter username and password manually.

UserName = InputBox("Enter Your User Name:", "Input User Name")

MySite.Login = UserName

UserPassword = InputBox("Enter Your Password:", "Input Password")

MySite.Password = UserPassword

MySite.SocksInfo = ""

MySite.ProxyInfo = ""

'Connecting to Remote Server

MySite.Connect

'****************** Section 4 ******************

‘You can skip this section if you don’t want to delete old files.

'Delete data from Download Folder (C:\Source Folder\FTP Files\)

    On Error Resume Next

    'If you want to delete particular extension file like all text files:

    Kill "C:\Source Folder\FTP Files\*.txt"

    'If you want to delete files containing some text or keywords like "batch":

    Kill "C:\Source Folder\FTP Files\*batch*"

    'If you want to delete all files in the folder:

    Kill "C:\Source Folder\FTP Files\*.*"

    On Error GoTo 0

'****************** Section 5 ******************

'It will create folder if it is not exist

If Len (Dir("C:\Source Folder\FTP Files\", vbDirectory)) = 0 Then

MkDir ("C:\Source Folder\FTP Files\")

End If

'****************** Section 6 ******************

'Downloading all files from ftp location to Download Folder (C:\Source Folder\FTP Files\)

'You can add multiple ftp location as below

Application.ScreenUpdating = False

MySite.DownloadAsync "/HOME/Data_Files/", "C:\Source Folder\FTP Files\"

   

Application.ScreenUpdating = False

MySite.DownloadAsync "/HOME/Data_New/", "C:\Source Folder\FTP Files\"

'****************** Section 7 ******************

'Pop-up message to show completion of downloading.

Application.ScreenUpdating = True

MsgBox ("All Files Downloaded")

End Sub

Step 4: Go to File >> Save menu or press Ctrl+S.

Step 5: Go to File >> Close and Return to Microsoft Excel menu or press Ctrl+Q.

How to Assign Shortcut key to VBA Macro?

Step 1: Go to Developer >> Macros menu.

A new small window will open then click on "Option" and another smaller window will open as in the screenshot.

Just press any key you want to create as shortcut key. For example, if you want that macro should be run by pressing Ctrl+G, then press G and click OK.

How to Assign Button to Macro?

Step 7: Go to Insert >> Shapes menu and select any shape you want to make it as Button and place anywhere in excel sheet.

Step 8: Right click on shape and click on "Assign Macro".

Step 9: A small window will open. Just select macro name and click OK.

Now if you will press Ctrl+G or click on button, macro will execute.

Some Extra Codes will help in this VBA Project

2^nd Way to Input UserName and Passowrd instead of entering manually.

In this way, you don’t need to input your username and password each and every time. You can input these in the script.

It will not give any pop-up to enter manually. Suppose username is "ftp_user" and password is "ftp_password" then replace code in Section 3 as below:

UserName = "ftp_user"

MySite.Login = UserName

UserPassword = "ftp_password"

MySite.Password = UserPassword

3^rd Way to Input UserName and Passowrd instead of entering manually.

In this way, you can input your username and password in any sheet and you can hide that sheet for your convenience. You will not require to change username and password in the script.

Suppose you have entered your username and password in sheet1 or any other sheet. You can rename sheet as per your choice. See screenshot below:

Replace this code in Section 3 as below:

UserName = Sheets("admin").Range("B1").Value

MySite.Login = UserName

UserPassword = Sheets("admin").Range("B2").Value

MySite.Password = UserPassword

Create folder on today date and download files in it.

If you want to download ftp files into today date folder, then this code will create folder in a directory and it will download files in it. For example, it will create date wise folder in “FTP Files” folder. Local Folder Location: C:\Source Folder\FTP Files\

You have to copy below two codes in their section.

Replace this code in Section 5 as below:

If Len (Dir("C:\Source Folder\FTP Files\" & Format(Date, "yyyymmdd"), vbDirectory)) = 0 Then

MkDir ("C:\Source Folder\FTP Files\" & Format(Date, "yyyymmdd"))

End If

Replace this code in Section 6 as below:

MySite.DownloadAsync "/HOME/Data_Files/" & "*", "C:\Source Folder\FTP Files\" & Format(Date, "yyyymmdd")

If you want to download files containing some text or keywords.

Suppose you want to download files from ftp location containing some text or keywords like "batch". It will download all files containing word “batch”.

Replace this code in Section 6 as below:

MySite.DownloadAsync "/HOME/Data_Files/" & "*batch*", "C:\Source Folder\FTP Files\" & Format(Date, "yyyymmdd")

You can also use text or keyword from excel sheet, just like we did to input username and password. Write keyword in cell B3 and place below codes:

Place this code in Section 1 as below:

Keyword = Sheets("admin").Range("B3").Value

Replace this code in Section 6 as below:

MySite.DownloadAsync "/HOME/Data_Files/" & "*" & Keyword & "*", "C:\Source Folder\FTP Files\" & Format(Date, "yyyymmdd")