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
- Download the component MSINET.OCX (from this link – non official – use it on own risk).
- From Excel VBA Project Editor (alt F11 from Excel sheet), Go to Menu -> Tools -> References -> Browse & choose the downloaded OCX file.
- 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 = "firstname.lastname@example.org" .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
- 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.
- 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).
- Then, from Command Prompt, use the command ‘FTP -i -s:D:\Command_File.txt‘.
- 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
10 thoughts on “Excel VBA – FTP Transfer – Files Download Upload”
please help how to create INET instance and how to reference Microsoft Internet Transfer .ocx ?
I have updated the Topic as per your query. Its been a bad presentation from me. Thanks for your time Hope it works now for you.
Still not works for me 🙁
Below is the Code i am using :
‘Variable Declarion Section
Dim FTP As INet
HostURL = ThisWorkbook.Sheets(1).Cells(1, 1)
FileSource = ThisWorkbook.Sheets(1).Cells(1, 2)
FileDestination = ThisWorkbook.Sheets(1).Cells(2, 2)
Set FTP = New INet
.URL = HostURL
.Protocol = icFTP
‘Replace with your Login and Password Below. Many FTP servers allow Anonymous access with below credentials
.UserName = “MY User Name ”
.Password = “Password”
.AccessType = icUseDefault
‘Use “Get” to Download and “Put” Option to Upload File to FTP Server
.Execute .URL, “Get ” & FileSource & ” ” & FileDestination
Do While http://FTP.StillExecuting
Application.Speech.Speak “Process Completed;” & http://FTP.ResponseInfo
If http://FTP.ResponseCode = 0 Then
MsgBox “File is Copied to :” & VBA.CurDir
Set FTP = Nothing
File format :
Could not guess what went wrong? might be the Folder path. Is it possible for your to give a Team viewer session with me?
ya sure , Could you please share your email id ? so that I can send Team viewer Details ..
can you plz help me, i am doing the same project and getting the error below. Please help me
Hi, I have Error 429 ActiveX can’t create object, any help please?
same here, any help ??
Did you try Step 2 mentioned in the above article?
If you still face the issues after verifying step2, give Teamviewer or Zoom access to connect to your computer & resolve issue.