Upload file via FTP from Excel VBA

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 wor开发者_如何转开发kstations that will need the program).

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)

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

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

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"

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

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