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.
Excel VBA FTP UploadVisual Basic1234567891011121314151617181920212223242526272829303132333435Sub Get_File_From_FTP()'Variable Declarion SectionDim FTP As Inet'Assign Host URL, Source and Destination File pathHostURL = 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 ParametersSet FTP = New InetWith 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 & " " & FileDestinationEnd WithDo While FTP.StillExecutingDoEventsLoop'Status of FTP through Voice MessageApplication.Speech.Speak "Process Completed;" & FTP.ResponseInfoIf 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 BoxMsgBox "File is Copied to :" & VBA.CurDirEnd IfSet FTP = NothingEnd Sub
- FTP server URL or IP address,
- User Name & Password,
- Remote File Name and
- Local File Name
- 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.
Shell123456open ftp.server.url.eduanonymouscd publcd D:\TestFoldermget *quit
- 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.
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
Excel VBA – FTP Transfer – Files Download Upload