从 Excel VBA 通过 FTP 上传文件

Question

需要从 Excel VBA 将文件 (file.txt) 上传到服务器 (ftp.server.com)。 （不一定是 FTP，只需要能够将文件放在那里并取回，并且我在 GoDaddy 共享托管上有一个服务器）

我尝试的是运行此脚本：

ftp -s:script.txt

：

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

script.txt 我得到的错误是：

425 无法打开与端口 53637 的数据连接：连接超时

Google 告诉我需要进入被动模式，但命令行 ftp.exe 客户端不允许这样做。

我是否有更简单的 FTP 替代方案，或者是否有更好的方法通过 VBA 上传文件（无需命令行解决方法）？

我正在考虑使用 DROPBOX（但我真的不想在所有需要该程序的工作站上安装该程序）。

Answer 1

如果您无法使用 Windows ftp.exe（特别是因为它不支持被动模式和 TLS/SSL），您可以使用另一个命令行 FTP 客户端。

例如，要使用 WinSCP 脚本 上传文件，请使用：

Call Shell( _
    "C:\path\WinSCP.com /log=C:\path\excel.log /command " & _
    """open ftp://user:[email protected]/"" " & _
    """put C:\path\file.txt /path/"" " & _
    """exit""")

---

为了方便阅读，上面运行了这些WinSCP 命令：

open ftp://user:[email protected]/
put C:\path\file.txt /path/
exit

您可以将命令放入脚本文件并使用 /script= 命令行参数，类似于ftp -s:，而不是/command。

---

请参阅将 Windows FTP 脚本转换为 WinSCP 脚本指南。

您甚至可以让 WinSCP GUI 为您生成 FTP 上传脚本。

---

WinSCP 默认为被动模式。

您还可以使用 FTPS (TLS/SSL)：

open ftpes://user:[email protected]/

---

或者您可以使用 来自 VBA 代码的通过 COM 的 WinSCP .NET 程序集。

---

（我是 WinSCP 的作者）

Answer 2

迭戈，我已经成功使用下面的代码很多年了。该代码从主机获取文件，但我确信可以对其进行修改以将文件放在那里。

'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

Answer 3

http://winscp.net 是免费的，可编写脚本，支持被动模式，绝对是优秀的。

Answer 4

经过大量研究，我找到了一种将文件上传到 FTP 位置的方法，无需任何 .ocx 文件互联网控制文件。这对我有用......

 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

请根据您的需要更改值

    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"

Answer 5

我也无法使被动模式与命令提示符一起使用，但我发现资源管理器工作得更快、更高效。

我在另一个子例程中指定了可以更改的内容：

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

以及我找到并修改为我使用的代码：

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

Answer 6

上面的脚本很棒，我使用以下命令上传文件并将输出记录到文件中，这在调试时很有用，而且 Windows ftp 无法执行被动模式是一个常见的误解，进入被动模式的命令是 ” quote pasv" （我已将其添加到脚本中

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