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]

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

Yep

 

[John_w]

    FTPcommand = "cmd /c ftp -n -s:" & Chr(34) & FTPcommandsFile & Chr(34)
    ret = wsh.Run(Command:=FTPcommand, WindowStyle:=0, WaitOnReturn:=True)  '0 = hide command window

 

[johnmpc]

    FTPcommand = "cmd /c ftp -n -s:" & Chr(34) & FTPcommandsFile & Chr(34)
    ret = wsh.Run(Command:=FTPcommand, WindowStyle:=0, WaitOnReturn:=True)  '0 = hide command window

Thank you. Big step forwards for my project complete!!

 

[johnmpc]

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"

Did you want to add this into the original code and repost so you can get the credit for the solution?

 

[John_w]

Did you want to add this into the original code and repost so you can get the credit for the solution?

Good idea - post #6 now has the updated code.

 

[johnmpc]

Good idea - post #6 now has the updated code.

Thanks Again.