How copy file from the local memory and save it to ftp server folder

Question

I have an excel workbook and want to copy another excel file from the local memory and save it to ftp server folder.

I found example code here here

The file which I want to transfer is following: C:\Users\User1\test.xlsx

The folder to which I want to transfer is accessible via: ftp://user:password@www.name/destination/

ftp = "85.253.158.128" I retrieved from https://www.myip.com/ (I tried both my IP and HOST)

My code is following:

Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" ( _
        ByVal hInternetSession As Long, ByVal sServerName As String, _
        ByVal nServerPort As Integer, ByVal sUserName As String, _
        ByVal sPassword As String, ByVal lService As Long, _
        ByVal lFlags As Long, ByVal lContext As Long) As Long
    Declare PtrSafe Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" ( _
        ByVal sAgent As String, ByVal lAccessType As Long, _
        ByVal sProxyName As String, _
        ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
    Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias _
     "FtpSetCurrentDirectoryA" (ByVal hFtpSession As Long, _
        ByVal lpszDirectory As String) As Boolean
    Declare PtrSafe Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" ( _
        ByVal hConnect As Long, _
        ByVal lpszLocalFile As String, _
        ByVal lpszNewRemoteFile As String, _
        ByVal dwFlags As Long, _
        ByRef dwContext As Long) As Boolean

Sub simpleFtpFileUpload()

    Dim ftp, FTP_PORT, user, password, loc_file, remote_file, ftp_folder As Variant
    ftp_folder = "ftp://user@www.name/destination"
    loc_file = "C:\Users\User1\test.xlsx"
    remote_file = ftp_folder & "/test.xlsx"
    FTP_PORT = 21
    user = "usr"
    password = "pwd"
    ftp = "85.253.158.128" '"192.168.1.110"

    Internet_OK = InternetOpen("", 1, "", "", 0)
    If Internet_OK Then
        FTP_OK = InternetConnect(Internet_OK, ftp, FTP_PORT, user, password, 1, 0, 0) ' INTERNET_DEFAULT_FTP_PORT or port no
        If FtpSetCurrentDirectory(FTP_OK, "/") Then
            Success = FtpPutFile(FTP_OK, loc_file, remote_file, FTP_TRANSFER_TYPE_BINARY, 0)
        End If
    End If
    If Success Then
        Debug.Print "ftp success ;)"
        MsgBox "ftp success ;)"
    Else
        Debug.Print "ftp failure :("
        MsgBox "ftp failure :("
    End If
End Sub

I always got an error "ftp failure :(". Could you help to solve this problem? I am also using VPN, may it trigger the problem?

UPDATE: Is it also possible to do something like below, because DestFile shows the path on the disc :

Sub simpleFtpFileUpload()

Dim SoureFile As String
Dim DestFile As String
SourceFile = "C:\Users\User1\test.xlsx"
DestFile = "ftp://user:password@www.name/destination/test.xlsx"
FileCopy SourceFile, DestFile

End Sub

With this approach I got an error

and FileCopy SourceFile, DestFile row is highlighted when I press Debug button.

Single-step through the code and check reply codes for each call — StureS, May 25 '21 at 12:20
What does "from local memory" should mean, please? Do you try referring to the file full name, or need to extract it from ClipBoard? — FaneDuru, May 25 '21 at 12:44
Without further analyzing your code, I see that you set the FTP port as a a string, "21". It should be an integer: https://learn.microsoft.com/en-us/windows/win32/api/wininet/nf-wininet-internetconnecta — StureS, May 25 '21 at 14:15
@StureS I identified that the error may come from `InternetConnect` as `FTP_OK` shows zero if run until `Success = FtpPutFile(...)` row. I also changed 21 to integr and tried to instantly change `FTP_PORT` to `INTERNET_DEFAULT_FTP_PORT` in `InternetConnect`. Additionally, I updated the question and added one extra VBA code, which also can transfer the file to new destination. — Priit Mets, May 26 '21 at 07:48
@StureS now it is working instead of `ftp = "85.253.158.128"` should be `ftp = "www.name"` from `ftp://user:password@www.name/destination/` — Priit Mets, May 26 '21 at 13:15

How copy file from the local memory and save it to ftp server folder

0 Answers0