在 VBA 中使用 FTP [英] Using FTP in VBA

问题描述

我编写了 VBA 代码，它基于 Excel 数据为 IBM 主机创建了一个带有 Job-Code 的 .txt 文件(Websphere MQ Define Job).

I wrote VBA code which creates a .txt file with Job-Code for an IBM host based on Excel data (Websphere MQ Define Job).

如果能够通过 FTP 自动将此文件传输到主机，那就太棒了.此时我通过以下方式手动执行此操作:

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:

(注释:LPAR = 主机名)

(comment: LPAR = Host-Name)

ftp <LPAR>
'user'
'password'
put 'dateiname'

它工作得很好.但我不知道如何将其转换为 VBA 代码.我在这里发现了一个类似的问题，并在那里发布了这个解决方案:

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:

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

我不确定我是否完全理解代码.我想我创建了一个包含用户数据和内容的虚拟文件 FtpComm.txt，并使用该文件打开连接并发送数据.

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.

不知何故，它一直有效

*SetAttr vPath & "FtpComm.txt", vbNormal*

我得到了错误

运行时错误:55 - 文件已打开.

Runtime-Error: 55 - File already opened.

此时已设置到 LPAR 的连接.但是SetAttr..."有什么作用呢?这是输入完成的地方吗?我该怎么办?

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?

推荐答案

两件事.

首先，您打开一个文件编号为 #fNum 的文件，该文件是从 fNum = FreeFile() 获得的，然后假设这将返回 1，如 Print #1 等:

First, you are opening a file under file number #fNum obtained from fNum = FreeFile(), and then just assuming that this will return 1, as in Print #1 etc.:

Open vPath & "FtpComm.txt" For Output As #fNum
Print #1, "user *******" ' your login and password"

将所有 Print #1 更改为 Print #fNum.

其次，您打开文件进行 I/O，但永远不要关闭它.所以当然，当你尝试修改它的属性或删除它时，系统会抱怨它已经打开并正在使用中.解决方案是关闭文件——一旦你完成对文件的写入/读取，你应该总是这样做.

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.

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"

至于你的代码在高层次上做了什么:如果你在命令行输入ftp -help，你会看到那些-n -i -g -s 标志的意思.所以是的，您正在将 FTP 命令写入文件 FtpComm.txt，然后通过 -s 标志将该文件提供给 ftp.

As for what your code is doing on a high level: if you type ftp -help at the command line, you will see what those -n -i -g -s flags mean. So yes, you're writing FTP commands to file FtpComm.txt, and then supplying that file to ftp via the -s flag.

-s:filename    Specifies a text file containing FTP commands; the
               commands will automatically run after FTP starts.

这篇关于在 VBA 中使用 FTP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！