Upload file via FTP from Excel VBA

Question

Need to upload a file (file.txt) to a server (ftp.server.com) from Excel VBA. (does not have to be necessarily FTP, just need to be able to put the file there and get it back, and I've got a server on GoDaddy shared hosting)

What I tried was to run this script:

ftp -s:script.txt

script.txt:

open ftp.server.com
USER
PASS
lcd c:\
put file.txt
disconnect
bye

The error I get is:

425 Could not open data connection to port 53637: Connection timed out

Google tells me I need to go to passive mode, but the command-line ftp.exe client doesn't allow that.

Do I have an easier alternative to FTP, or is there a better way to upload a file via VBA (without the command-line workaround)?

I'm thinking about using DROPBOX (but I really don't want to have to install this program on all the workstations that will need the program).

Martin Prikryl · Answer 1 · 2022-09-13T05:19:29.317

If you cannot use the Windows ftp.exe (particularly because it does not support the passive mode and TLS/SSL), you can use another command-line FTP client.

For example to upload a file using WinSCP scripting, use:

Call Shell( _
    "C:\path\WinSCP.com /log=C:\path\excel.log /command " & _
    """open ftp://user:password@example.com/"" " & _
    """put C:\path\file.txt /path/"" " & _
    """exit""")

To ease reading, the above runs these WinSCP commands:

open ftp://user:password@example.com/
put C:\path\file.txt /path/
exit

You can put the commands to a script file and run the script with /script= command-line parameter, similarly to the ftp -s:, instead of the /command.

See the guide to Converting Windows FTP script to WinSCP script.

You can even have WinSCP GUI generate the FTP upload script for you.

WinSCP defaults to the passive mode.

You can also use FTPS (TLS/SSL):

open ftpes://user:password@example.com/

Alternatively you can use WinSCP .NET assembly via COM from the VBA code.

(I'm the author of WinSCP)

Sorry.. https://stackoverflow.com/questions/51762332/upload-file-through-vba-and-winscp-com — Andreas, Aug 09 '18 at 08:34

Jeremy · Answer 2 · 2011-10-14T23:07:05.897

Diego, I've used the code below successfully for years. The code gets files from the host, but I'm sure it can be modified to put files there instead.

'Start Code
Set FSO = CreateObject("scripting.filesystemobject")

'**************************************************************************************    '***        Create FTP Action File & Initiate FTP File Transfer
'**************************************************************************************    VREDET = filename1 'Variable holding name of file to get

F = "C:\Volume\Temp\FTPScript.txt" 'creates the file that holds the FTP commands

Open F For Output As #1
Print #1, "open ftp.server" 'replace ftp.server with the server address
Print #1, ID 'login id here
Print #1, PW 'login password here
Print #1, "cd " & " Folder1" 'Directory of file location
Print #1, "cd " & " Folder2" 'Sub-Directory of file location
Print #1, "ascii"
Print #1, "prompt"
'Get the file from the host and save it to the specified directory and filename
Print #1, "get " & VREDET; " C:\some\directory\" & another-filename & ".CSV"
Print #1, "disconnect" 'disconnect the session
Print #1, "bye"
Print #1, "exit"
Close #1

'identify folder where ftp resides and execute the FTPScript.txt file
'vbHide - hides the FTP session

If FSO.FolderExists("C:\Windows\System32") = False Then
    Shell "C:\WINNT\system32\ftp.exe -s:C:\Volume\Temp\FTPScript.txt", vbHide
Else
    Shell "C:\WINDOWS\system32\ftp.exe -s:C:\Volume\Temp\FTPScript.txt", vbHide
End If
'end code

Native windows FTP does not support passive mode and that's a showstopper when you need it. — iDevlop, Oct 15 '11 at 19:27

score 3 · Accepted Answer · answered Oct 12 '11 at 13:01

3

http://winscp.net is free, scriptable, supports passive mode and is definitely EXCELLENT.

answered Oct 12 '11 at 13:01

iDevlop

24,841
11
90
149

14

I Cannot understand why this is the accepted answer ? The guy asked for VBA inside excel, not for desktop software! For desktop FileZilla is the best and most used one and works on every platform: Windows, Linux, Mac: https://filezilla-project.org/ – Nikolay Ivanov Jun 14 '13 at 11:06
2

@NikolayIvanov: "Filezilla is the best" is a very subjective point. AFAIK, FZ is not scriptable which makes it unusable for many situations. That is a fact :-) – iDevlop Jun 14 '13 at 13:28
1

@NikolayIvanov: "the guy" - as you say - probably understood the answer better than you do. My suggestion precisely answered his needs. – iDevlop Oct 14 '14 at 09:02
I don't want to argue with you which is the best desktop ftp client. This is not a forum. VBA means Visual Basic for Applications and is run from inside macro-enabled Excel worksheet, usually .xlsm, I'm using it often for various reasons, including scraping data from our customers' websites, listing on eBay and Amazon and a lot more. That's why I think that YOU did NOT understand what Diego Castro was asking for. – Nikolay Ivanov Oct 15 '14 at 10:14
3

I've posted an [answer with an example how to use WinSCP](http://stackoverflow.com/a/32004195/850848). – Martin Prikryl Aug 14 '15 at 07:06
3

@NikolayIvanov I agree with you mate, this answer should not have been accepted – Anarach Oct 08 '15 at 13:29

score 2 · Answer 4 · edited Dec 13 '19 at 07:14

After lot of research I found a method to upload file to FTP location without any .ocx file internet control file. This worked for me....

 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 = "/EXPORT"
    loc_file = ThisWorkbook.Path & "\readme.txt"
    remote_file = ftp_folder & "/readme.txt"
    FTP_PORT = "2221"
    user = "ajay"
    password = "ajay"
    ftp = "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

Please change values as per your needs

    ftp_folder = "/EXPORT"
    loc_file = ThisWorkbook.Path & "\readme.txt"
    remote_file = ftp_folder & "/readme.txt"
    FTP_PORT = "2221"
    user = "ajay"
    password = "ajay"
    ftp = "192.168.1.110"

score 0 · Answer 5 · answered Jul 22 '21 at 22:27

I could not make the passive mode work with the command prompt either, but I found out explorer works faster and more efficiently.

I have specified in an other subroutine what could change:

strFileName = "file.txt"
strMyFile = "C:\path\file.txt"
strFTP = "ftp.server.com"
strUser = "ID"
strPW = "PWD"
strSubfolder = "/subfolder"

And the code I found and modified to my use:

Sub cmdFTPviaExplorer()

    Set oShell = CreateObject("Shell.Application")
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Const copyType = 16

    strFTP = "ftp://" & strUser & ":" & strPW & "@" & strFTP & strSubfolder

    Set objFTP = oShell.Namespace(strFTP)

    'Upload single file
    If objFSO.FileExists(strMyFile) Then
        Set objFile = objFSO.getFile(strMyFile)
        strParent = objFile.ParentFolder
        Set objFolder = oShell.Namespace(strParent)
        Set objItem = objFolder.ParseName(objFile.Name)
        objFTP.CopyHere objItem, copyType
    End If

    'Pop-up message box
    MsgBox strFileName & " file created and uploaded"

End Sub

Chait E · Answer 6 · 2014-08-19T15:11:14.937

The above script is great I used the following commands to upload files as well as log the output to a file which is useful when debugging also it is a common misconception that windows ftp cannot do passive mode the command to go passive is "quote pasv" (I have added this to the script

Sub FtpFileto()
    Set FSO = CreateObject("scripting.filesystemobject")
    F = "C:\FTPScript.txt"
    ' Create the ftpscript to be run

    Open F For Output As #1
    Print #1, "open ftp.server.com" 'replace ftp.server with the server address
    Print #1, "ID" 'login id here
    Print #1, "PWD" 'login password here
    Print #1, "quote pasv" ' passive mode ftp if needed
    Print #1, "cd " & " /dir" 'Directory of file location
    Print #1, "cd " & " subdir" 'Sub-Directory of file location
    Print #1, "ascii"
    Print #1, "prompt"
    'Put the file from the host and save it to the specified directory and filename
    Print #1, "put " & VREDET; """C:\file1.csv"""; ""
    Print #1, "put " & VREDET; """C:\file2.csv"""; ""
    Print #1, "put " & VREDET; """C:\file3.csv"""; ""
    Print #1, "disconnect" 'disconnect the session
    Print #1, "bye"
    Print #1, "exit"
    Close #1
    'Now for the command to upload to the ftpsite and log it to a text file
    ' the trick is to use the standard command shell which allows logging

    Shell "cmd /c C:\WINDOWS\system32\ftp.exe -i -s:C:\FTPScript.txt > c:\ftpuploadlog.txt", vbHide

    End Sub

[The Windows `ftp.exe` does not support passive mode](http://stackoverflow.com/q/18643542/850848). Using `quote pasv` cannot help. — Martin Prikryl, Aug 14 '15 at 06:59

Upload file via FTP from Excel VBA

6 Answers6

Linked