Download File from FTP site using VBA

[johnmpc]

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.

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

 

[johnmpc]

Any thoughts please?

 

[John_w]

Your code works for me.

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.

 

[johnmpc]

Your code works for me.

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.

 

[johnmpc]

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?

 

[John_w]

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.

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

 

[johnmpc]

Thanks @John_w

I'm getting an error here. (bad file name or number)

  Open FTPcommandsFile For Output As #filenum

I assume i need to do something with this line.

FTPcommandsFile = ThisWorkbook.Path & "\FTP_commands.txt"

what do i need to do?

 

[John_w]

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:

    FTPcommandsFile = Environ("temp") & "\FTP_commands.txt"

 

[johnmpc]

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.

 

[John_w]

Which pop-up? Do you mean the command window?