Copy Files From FTP Location Using VBA Script
Suppose you want to download files from single or multiple FTP directories to the local folder, then this code will help you. You can download all available files or particular files from the 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 the key Alt+F11. It will open Microsoft Visual Basic for Applications Window.
Step 2: Go to Insert >> Module menu.
Step 3: Copy the below code and paste it into the code window:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | 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 the CuteFTPPro window application. 'Create FTEConnection object Set MySite = CreateObject("CuteFTPPro.TEConnection") 'Connect FTP Server 'It will connect the remote server hostname, 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. '1st 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 a 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 a 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 the Microsoft Excel menu or press Ctrl+Q.
Assign a Shortcut key to VBA Macro
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 a shortcut key. For example, if you want that macro should be run by pressing Ctrl+G, then press G and click OK.
Assign Button to Macro
Step 1: Go to Insert >> Shapes menu and select any shape you want to make as Button and place it anywhere in the excel sheet.
Step 2: Right-click on the shape and click on Assign Macro.
Step 3: A small window will open. Just select a macro name and click OK.
Now if you will press Ctrl+G or click on the button, the macro will execute.
Some Extra Codes will help in this VBA Project
2nd Way to Input UserName and Password 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 the username is “ftp_user” and the password is “ftp_password” then replace the code in Section 3 row number 34 to 38 as below:
1 2 3 4 5 | UserName = "ftp_user" MySite.Login = UserName UserPassword = "ftp_password" MySite.Password = UserPassword |
3rd Way to Input UserName and Password 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 your username and password in the script.
Suppose you have entered your username and password in sheet1 or any other sheet. You can rename the sheet as per your choice. See the screenshot below:
Replace this code in Section 3 row number 34 to 38 as below:
1 2 3 4 5 | UserName = Sheets(“admin”).Range(“B1”).Value MySite.Login = UserName UserPassword = Sheets(“admin”).Range(“B2”).Value MySite.Password = UserPassword |
Create a folder on today’s date and download files in it
If you want to download FTP files into today’s date folder, then this code will create a folder in a directory and it will download files in it. For example, it will create date wise folder in the 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 row number 64 to 66 as below:
1 2 3 | 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 |
And replace this code in Section 6 row number 72 to 76 as below:
1 | MySite.DownloadAsync "/HOME/Data_Files/" & "*", "C:\Source Folder\FTP Files" & Format(Date, "yyyymmdd") |
To download files containing some text or keywords
Suppose you want to download files from the FTP location containing some text or keywords like “batch”. It will download all files containing the word “batch”.
Replace this code in Section 6 row number 72 to 76 as below:
1 | MySite.DownloadAsync "/HOME/Data_Files/" & "*batch*", "C:\Source Folder\FTP Files" & Format(Date, "yyyymmdd") |
You can also use text or keywords from the excel sheet, just like we did to input the username and password. Write the keyword in cell B3 and place the below codes:
Add this code in Section 1 row number 8 as below:
1 | Keyword = Sheets("admin").Range("B3").Value |
And replace this code in Section 6 row number 72 to 76 as below:
1 | MySite.DownloadAsync "/HOME/Data_Files/" & "*" & Keyword & "*", "C:\Source Folder\FTP Files" & Format(Date, "yyyymmdd") |
0 Comments