在 VBA 中使用 FTP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7185844/
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
Using FTP in VBA
提问by Ocalion
I wrote VBA code which creates a .txt file with Job-Code for an IBM host based on Excel data (Websphere MQ Define Job).
我编写了 VBA 代码，它基于 Excel 数据（Websphere MQ 定义作业）为 IBM 主机创建了一个带有作业代码的 .txt 文件。
It would be cool to have the possibility to tranfer this file to the host automatically via FTP. At this point I do this manually via:
有可能通过 FTP 自动将此文件传输到主机会很酷。在这一点上，我通过以下方式手动执行此操作：
(comment: LPAR = Host-Name)
（注释：LPAR = 主机名）
ftp <LPAR>
'user'
'password'
put 'dateiname'
It works quite fine. But I don't know how to tranfer this to the VBA code. I found a similiar question here and there this solution was posted:
它工作得很好。但我不知道如何将其转移到 VBA 代码。我在这里找到了一个类似的问题，并在那里发布了这个解决方案：
Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long
vPath = ThisWorkbook.path
vFile = "path"
vFTPServ = "<LPAR>"
'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user *******" ' your login and password"
'Print #1, "cd TargetDir" 'change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program Close
Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ, vbNormalNoFocus
SetAttr vPath & "\FtpComm.txt", vbNormal
Kill vPath & "\FtpComm.txt"
End Sub
I'm not sure if I understand the code completely. I think I create a dummy file, FtpComm.txt, with the user data and the content and use this file to open the connection and send the data.
我不确定我是否完全理解代码。我想我创建了一个包含用户数据和内容的虚拟文件 FtpComm.txt，并使用此文件打开连接并发送数据。
It works, somehow, until the point
它以某种方式起作用，直到这一点
*SetAttr vPath & "\FtpComm.txt", vbNormal*
There I get the error
在那里我得到了错误
Runtime-Error: 55 - File already opened.
运行时错误：55 - 文件已打开。
The connection to the LPAR is set at this point. But what does "SetAttr..." do? Is this the point where the Input is done? What should I do?
到 LPAR 的连接在此时设置。但是“SetAttr...”有什么作用呢？这是输入完成的点吗？我该怎么办？
回答by Jean-Fran?ois Corbett
Two things.
两件事情。
First, you are opening a file under file number #fNumobtained from fNum = FreeFile(), and then just assuming that this will return 1, as in Print #1etc.:
首先，您在#fNum从 获得的文件号下打开一个文件fNum = FreeFile()，然后假设这将返回 1，例如Print #1：
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user *******" ' your login and password"
Change all your Print #1to Print #fNum.
将您的所有更改Print #1为Print #fNum.
Second, you open your file for I/O, but never close it. So of course, when you try to modify its attributes or delete it, the system will complain that it's already open and in use. The solution is to close the file -- which you should always do as soon as you're done writing to/reading from a file.
其次，您为 I/O 打开文件，但永远不要关闭它。所以当然，当你尝试修改它的属性或删除它时，系统会抱怨它已经打开并在使用中。解决方案是关闭文件——您应该在完成对文件的写入/读取后立即执行此操作。
Close #fNum ' add this line
' Can now manipulate the file without risking conflict
Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ, vbNormalNoFocus
SetAttr vPath & "\FtpComm.txt", vbNormal
Kill vPath & "\FtpComm.txt"
As for what your code is doing on a high level: if you type ftp -helpat the command line, you will see what those -n -i -g -sflags mean. So yes, you're writing FTP commands to file FtpComm.txt, and then supplying that file to ftpvia the -sflag.
至于你的代码在高层次上做什么：如果你ftp -help在命令行中输入，你会看到这些-n -i -g -s标志的含义。所以是的，您正在将 FTP 命令写入 file FtpComm.txt，然后ftp通过-s标志提供该文件。
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
回答by ray
You're on the right path and following Jean-Fran?ois's answer should get you there.
您走在正确的道路上，按照 Jean-Fran?ois 的回答应该可以帮助您实现目标。
If you want something you can cut and paste, you can try the code below. It's a variation of something I wrote several years ago.
如果你想要一些可以剪切和粘贴的东西，你可以试试下面的代码。这是我几年前写的东西的变体。
The biggest caveat is that once you shell the FTP command, you have no idea if the file actually transferred until you manually check.
最大的警告是，一旦您对 FTP 命令进行 shell，在您手动检查之前，您不知道文件是否实际传输。
But, if you're limited to strict VBA, then this is the way to go:
但是，如果您仅限于严格VBA，那么这就是要走的路：
Option Explicit
Const FTP_ADDRESS = "ftp.yourdestination.com"
Const FTP_USERID = "anon"
Const FTP_PASSWORD = "anon"
Sub Macro1()
If Not SendFtpFile_F() Then
MsgBox "Could not ftp file"
Else
MsgBox "Sent"
End If
End Sub
Function SendFtpFile_F() As Boolean
Dim rc As Integer
Dim iFreeFile As Integer
Dim sFTPUserID As String
Dim sFTPPassWord As String
Dim sWorkingDirectory As String
Dim sFileToSend As String
Const FTP_BATCH_FILE_NAME = "myFtpFile.ftp"
Const INCREASED_BUFFER_SIZE = 20480
SendFtpFile_F = False
sWorkingDirectory = "C:\YourWorkingDirectory\"
sFileToSend = "NameOfFile.txt"
On Error GoTo FtpNECAFile_EH
'Kill FTP process file if it exists
If Dir(sWorkingDirectory & FTP_BATCH_FILE_NAME) <> "" Then
Kill sWorkingDirectory & FTP_BATCH_FILE_NAME
End If
'Create FTP process file
iFreeFile = FreeFile
Open sWorkingDirectory & FTP_BATCH_FILE_NAME For Output As #iFreeFile
Print #iFreeFile, "open " & FTP_ADDRESS
Print #iFreeFile, FTP_USERID
Print #iFreeFile, FTP_PASSWORD
Print #iFreeFile, "mput " & sWorkingDirectory & sFileToSend
Print #iFreeFile, "quit"
Close #iFreeFile
'Shell command the FTP file to the server
Shell "ftp -i -w:20480 -s:" & sWorkingDirectory & FTP_BATCH_FILE_NAME
SendFtpFile_F = True
GoTo FtpNECAFile_EX
FtpNECAFile_EH:
MsgBox "Err", Err.Name
FtpNECAFile_EX:
Exit Function
End Function
Note: While stepping through code, you could type the following command in the immediate window and then copy/paste the results into a Command Window:
注意：在单步执行代码时，您可以在即时窗口中键入以下命令，然后将结果复制/粘贴到命令窗口中：
? Shell "ftp -i -w:20480 -s:" & sWorkingDirectory & FTP_BATCH_FILE_NAME
回答by Edwin de Koning
SetAttr is used to set the attributes of a file (or a folder). In your case it sets it back to Normal (so not hidden, readonly, etc.). However, since your file is still open from the 'Open For Output' call it will fail with the 'File already opened' error.
SetAttr 用于设置文件（或文件夹）的属性。在您的情况下，它将其设置回正常（因此不是隐藏、只读等）。但是，由于您的文件仍然从“打开输出”调用中打开，它会因“文件已打开”错误而失败。
I don't see any reason why the SetAttr function is called though, as you are not changing the attributes anywhere else. So feel free to remove it and see if it still works.
我看不出调用 SetAttr 函数的任何原因，因为您没有在其他任何地方更改属性。所以请随意删除它，看看它是否仍然有效。
回答by Fernando Toro
In the first example, after the sentence
在第一个例子中，在句子之后
Print #1, "quit" ' Quit ftp program Close
you must close the file used for connection with the sentence:
您必须关闭用于连接句子的文件：
Close #fNum
Because this file is currently used.
因为这个文件是当前使用的。
