通过FTP从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)

需要从Excel VBA将文件(file.txt)上载到服务器(ftp.server.com)。 (不一定是FTP,只需要能够将文件放在那里并将其取回,我在GoDaddy共享主机上有一台服务器)

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

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

Google tells me I need to go to passive mode, but the command-line ftp.exe client 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(但我真的不想在所有需要该程序的工作站上安装此程序)。

4 个解决方案

#1

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

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

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

如果您无法使用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:password@example.com/"" " & _
    """put C:\path\file.txt /path/"" " & _
    """exit""")

To ease reading, the above runs these WinSCP commands:

为了便于阅读,上面运行了这些WinSCP命令:

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.

您可以将命令放入脚本文件并使用/ script = command-line参数运行脚本,类似于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 script for you.

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

WinSCP defaults to the passive mode.

WinSCP默认为被动模式。

You can also use FTPS (TLS/SSL):

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

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

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

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

(I'm the author of WinSCP)

(我是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

-1

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不能做被动模式,命令去被动是“引用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

#1