Download File from FTP site using VBA

johnmpc

Board Regular
Joined
Oct 19, 2020
Messages
108
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I've gone as far as i can with this one. Now need some assistance please.
I would like to automate the download of a file from the a ftp location.
Nothing sensitive in here so here's the username and password too.

ftp://ftpdata.btcactivewear.co.uk/ProductExport_mpc0001.zip

Username "mpc0001", Password "T5smB<+uMCyr"

I have this code all in a module.
The code seems to run but no file is downloaded. But the target folder shows as having been modified.
?‍♂️?‍♂️ Thanks in advance.

VBA Code:
Private Const FTP_TRANSFER_TYPE_UNKNOWN     As Long = 0
Private Const INTERNET_FLAG_RELOAD          As Long = &H80000000

Private Declare PtrSafe Function InternetOpenA Lib "wininet.dll" ( _
    ByVal sAgent As String, _
    ByVal lAccessType As Long, _
    ByVal sProxyName As String, _
    ByVal sProxyBypass As String, _
    ByVal lFlags As Long) As LongPtr

Private Declare PtrSafe Function InternetConnectA Lib "wininet.dll" ( _
    ByVal hInternetSession As LongPtr, _
    ByVal sServerName As String, _
    ByVal nServerPort As Long, _
    ByVal sUsername As String, _
    ByVal sPassword As String, _
    ByVal lService As Long, _
    ByVal lFlags As Long, _
    ByVal lcontext As Long) As LongPtr

Private Declare PtrSafe Function FtpGetFileA Lib "wininet.dll" ( _
    ByVal hConnect As LongPtr, _
    ByVal lpszRemoteFile As String, _
    ByVal lpszNewFile As String, _
    ByVal fFailIfExists As Long, _
    ByVal dwFlagsAndAttributes As Long, _
    ByVal dwFlags As Long, _
    ByVal dwContext As Long) As LongPtr

Private Declare PtrSafe Function InternetCloseHandle Lib "wininet" ( _
    ByVal hInet As LongPtr) As LongPtr


Sub FtpDownload(ByVal strRemoteFile As String, ByVal strLocalFile As String, ByVal strHost As String, ByVal lngPort As Long, ByVal strUser As String, ByVal strPass As String)
    Dim hOpen   As LongPtr
    Dim hConn   As LongPtr

    hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1)
    hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2)

    If FtpGetFileA(hConn, strRemoteFile, strLocalFile, 1, 0, FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, 0) Then
        Debug.Print "Success"
    Else
        Debug.Print "Fail"
    End If

    'Close connections
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
End Sub
Sub TestDownload()
  FtpDownload "/ProductExport_mpc0001.zip", "C:\Users\JohnGlanville\OneDrive - MPC Embroidery\Desktop\Customers\BTCTest\ProductExport_mpc0001.zip", _
              "ftpdata.btcactivewear.co.uk", 21, "mpc0001", "T5smB<+uMCyr"
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Your code works for me.
VBA Code:
Sub TestDownload()
    FtpDownload "/ProductExport_mpc0001.zip", "C:\Temp\ProductExport_mpc0001.zip", _
                "ftpdata.btcactivewear.co.uk", 21, "mpc0001", "T5smB<+uMCyr"              
End Sub
Maybe the OneDrive folder is the problem.
 
Upvote 0
Your code works for me.
VBA Code:
Sub TestDownload()
    FtpDownload "/ProductExport_mpc0001.zip", "C:\Temp\ProductExport_mpc0001.zip", _
                "ftpdata.btcactivewear.co.uk", 21, "mpc0001", "T5smB<+uMCyr"             
End Sub
Maybe the OneDrive folder is the problem.


Thanks for your reply. I'll try something different but I use a onedrive folder for a direct download from a website without any issues.
i think the issue lies in the format of the ftp address or something maybe.
 
Upvote 0
Very strange, It appears to run sometimes, but not always, and there is never a file actually downloaded. Frustrating.

Do you actually get the file to download?

Are there some settings to change for FTPs or internet use within VBA?
 
Upvote 0
Yes, the file is downloaded.

Try this equivalent ftp command line method. The cmd /k keeps the command window open so that you can see any errors, but you must close it manually to continue VBA code execution.

VBA Code:
Public Sub Test_Download()
    Ftp_Download_File "/ProductExport_mpc0001.zip", "C:\Temp\ProductExport_mpc0001.zip", _
                      "ftpdata.btcactivewear.co.uk", 21, "mpc0001", "T5smB<+uMCyr"
End Sub


Public Sub Ftp_Download_File(ByVal strRemoteFile As String, ByVal strLocalFile As String, ByVal strHost As String, ByVal lngPort As Long, ByVal strUser As String, ByVal strPass As String)

    Dim FTPcommandsFile As String
    Dim filenum As Integer
    Dim FTPcommand As String
    Dim ret As Long
 
'    Dim wsh As WshShell    'Early bound object - requires reference to Windows Script Host Object Model
'    Set wsh = New WshShell
 
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")

    If Dir(strLocalFile) <> vbNullString Then Kill strLocalFile
 
    FTPcommandsFile = Environ("temp") & "\FTP_commands.txt"
    filenum = FreeFile
    Open FTPcommandsFile For Output As #filenum
    Print #filenum, "open " & strHost & " " & lngPort
    Print #filenum, "user " & strUser & " " & strPass
    Print #filenum, "lcd " & Chr(34) & Left(strLocalFile, InStrRev(strLocalFile, "\") - 1) & Chr(34)      'set local folder
    Print #filenum, "binary"
    Print #filenum, "get " & Chr(34) & strRemoteFile & Chr(34)
    Print #filenum, "bye"
    Close #filenum
 
    'Either, normal use:
    FTPcommand = "cmd /c ftp -n -s:" & Chr(34) & FTPcommandsFile & Chr(34)
    ret = wsh.Run(Command:=FTPcommand, WindowStyle:=0, WaitOnReturn:=True)  '0 = hide command window
 
    'Or, for debugging - cmd /k keeps the command window open; must close it manually to continue VBA execution
    'FTPcommand = "cmd /k ftp -n -s:" & Chr(34) & FTPcommandsFile & Chr(34)
    'ret = wsh.Run(Command:=FTPcommand, WindowStyle:=5, WaitOnReturn:=True) '5 = show command window
 
    Kill FTPcommandsFile
 
    If Dir(strLocalFile) <> vbNullString Then
        Debug.Print "Successfully downloaded " & strLocalFile
    Else
        Debug.Print "Failed to download " & strLocalFile
    End If
 
End Sub
 
Last edited:
Upvote 0
Solution
Thanks @John_w

I'm getting an error here. (bad file name or number)
VBA Code:
  Open FTPcommandsFile For Output As #filenum

I assume i need to do something with this line.
VBA Code:
FTPcommandsFile = ThisWorkbook.Path & "\FTP_commands.txt"

what do i need to do?
 
Upvote 0
If the workbook is saved in a OneDrive folder and that has limited write access rights then the macro can't create the .txt file, which would cause that error. Try changing the line to:
VBA Code:
    FTPcommandsFile = Environ("temp") & "\FTP_commands.txt"
 
Upvote 0
That works a treat!!! Thank you, What needs changed to stop the pop up now?



ftp> open ftpdata.btcactivewear.co.uk 21
Connected to ftpdc.btcint.net.
220 Welcome to the BTC activewear FTP server
200 Always in UTF8 mode.
ftp> user mpc0001 T5smB<+uMCyr
331 Please specify the password.
230 Login successful.
ftp> lcd "C:\Temp\Pricelists"
Local directory now C:\Temp\Pricelists.
ftp> binary
200 Switching to Binary mode.
ftp> get "/ProductExport_mpc0001.zip"
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for /ProductExport_mpc0001.zip (4474374 bytes).
226 Transfer complete.
ftp: 4474374 bytes received in 3.10Seconds 1441.49Kbytes/sec.
ftp> bye
221 Goodbye.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top