Copy Files From FTP Location Using VBA Script

copy-files-from-ftp-location-using-vba-simplemsoffice

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.

ftpassignmacro

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:

assing-username-password-vba-macro

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

Leave a Comment

Your email address will not be published. Required fields are marked *