从 Excel VBA 通过 FTP 上传文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7737691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Upload file via FTP from Excel VBA
提问by Diego Castro
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)
需要从 Excel VBA 上传文件 (file.txt) 到服务器 (ftp.server.com)。（不一定是 FTP，只需要能够将文件放在那里并取回，我在 GoDaddy 共享主机上有一个服务器）
What I tried was to run this script:
我试图运行这个脚本：
ftp -s:script.txt
script.txt:
脚本.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
425 无法打开到端口 53637 的数据连接：连接超时
Google tells me I need to go to passive mode, but the command-line ftp.execlient doesn't allow that.
谷歌告诉我我需要进入被动模式，但命令行ftp.exe客户端不允许。
Anyone know of any free (open source) command-line FTP client that allows passive mode?
任何人都知道任何允许被动模式的免费（开源）命令行 FTP 客户端？
Do I have an easier alternative to FTP?
我有比 FTP 更简单的替代方法吗？
Is there a better way to upload a file via VBA (without the command-line workaround)?
有没有更好的方法通过 VBA 上传文件（没有命令行解决方法）？
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).
我正在考虑使用 DROPBOX（但我真的不想在所有需要该程序的工作站上安装该程序）。
回答by Martin Prikryl
If you cannot use the Windows ftp.exe(particularly because it does not support the passive modeand TLS/SSL), you can use another command-line FTP client.
如果您不能使用 Windows ftp.exe（特别是因为它不支持被动模式和 TLS/SSL），您可以使用另一个命令行 FTP 客户端。
For example to upload a file using WinSCP scripting, use:
例如，要使用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""")
To ease reading, the above runs these WinSCP commands:
为了便于阅读，上面运行了这些 WinSCP 命令：
open ftp://user:[email protected]/
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.
您可以将命令放入脚本文件并使用/script=命令行参数运行脚本，类似于ftp -s:, 而不是/command.
See the guide to Converting Windows FTP script to WinSCP script.
请参阅将 Windows FTP 脚本转换为 WinSCP 脚本的指南。
You can even have WinSCP GUI generate the FTP upload scriptfor you.
您甚至可以让WinSCP GUI为您生成 FTP 上传脚本。
WinSCP defaults to the passive mode.
WinSCP 默认为被动模式。
You can also use FTPS (TLS/SSL):
您还可以使用FTPS (TLS/SSL)：
open ftpes://user:[email protected]/
Alternatively you can use WinSCP .NET assembly via COMfrom the VBA code.
或者，您可以通过VBA 代码中的COM使用WinSCP .NET 程序集。
(I'm the author of WinSCP)
（我是 WinSCP 的作者）
回答by Jeremy
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
回答by Patrick Honorez
http://winscp.netis free, scriptable, supports passive mode and is definitely EXCELLENT.
http://winscp.net是免费的，可编写脚本的，支持被动模式，绝对是优秀的。
回答by Ajay Anil Zori
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....
经过大量研究，我找到了一种无需任何 .ocx 文件 Internet 控制文件即可将文件上传到 FTP 位置的方法。这对我有用....
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"
回答by Chait E
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
上面的脚本很棒我使用以下命令上传文件并将输出记录到一个文件中，这在调试时很有用，这是一个常见的误解，即 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
