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.

Anyone know of any free (open source) command-line FTP client that allows passive mode?

Do I have an easier alternative to FTP?

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).

回答1:

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

回答2:

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:[e-mail]/"" " & _
    """put C:\path\file.txt /path/"" " & _
    """exit""")

---

To ease reading, the above runs these WinSCP commands:

open ftp://user:[e-mail]/
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:[e-mail]/

---

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

---

(I'm the author of WinSCP)

回答3:

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

回答4:

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"

回答5:

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