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

问题描述

我写了VBA代码，它使用基于Excel数据（Websphere MQ Define Job）的IBM主机的Job-Code创建一个.txt文件。

很酷，有可能通过FTP自动将此文件传输到主机。在这一点上，我手动执行：

（注释：LPAR =主机名）

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

  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> 
 
'ftp.exe的安装文件命令
 fNum = FreeFile（）
打开vPath& \FtpComm.txt输出为#fNum 
打印＃1，用户*******您的登录名和密码
'打印＃1，cd TargetDir在服务器上更改为dir 
打印＃1，bin'bin或ascii文件类型以发送
打印＃1，put& vPath&\& vFile& & vFile将本地文件名上传到服务器文件
打印＃1，关闭关闭连接
打印＃1，退出退出ftp程序关闭
 
 Shell ftp -n -i -g -s：& vPath&\FtpComm.txt& vFTPServ，vbNormalNoFocus 
 
 SetAttr vPath&\FtpComm.txt，vbNormal 
 Kill vPath&\FtpComm.txt
 
 End Sub 
  

$ b $我不知道我是否完全了解代码，我想我用用户数据和内容创建一个虚拟文件FtpComm.txt，并使用该文件打开连接并发送数据。 p>

它以某种方式工作，直到点

  * SetAttr vPath&\FtpComm.txt，vbNormal * 
  

我收到错误

运行时错误：55 - 文件已打开。

此时与LPAR的连接已设置。但是SetAttr ...做什么？这是输入的完成点吗？我应该怎么办？

解决方案

两件事。

您正在从 fNum = FreeFile（）获取的文件号 #fNum 下打开一个文件，然后假设将返回1，如打印＃1 等：

 打开vPath& \FtpComm.txt输出为#fNum 
打印＃1，用户*******您的登录名和密码
  

将所有打印＃1 更改为打印#fNum 。

其次，您打开文件进行I / O，但不要关闭它，所以当然，当您尝试修改其属性或删除它时，系统将会抱怨它已经开放并被使用了，解决方案是关闭文件 - 一旦你完成写入/从文件读取，你应该一直执行这个文件。

 关闭#fNum'添加此行
'现在可以操作该文件而不冒险冲突
 
 Shellftp -n -i -g -s：& vPath&\FtpComm.txt& vFTPServ，vbNormalNoFocus 
 
 SetAttr vPath&\FtpComm.txt，vbNormal 
 Kill vPath& \FtpComm.txt
  

关于你的代码在高层上做什么：如果你在命令行中键入 ftp -help ，你会看看这些 -n -i -g -s 标志是什么意思。所以是的，你正在写文件 FtpComm.txt 的FTP命令，然后通过该文件将该文件提供给 ftp -s 标志。

  -s：filename指定一个文本文件， FTP命令
命令将在FTP启动后自动运行。 
  

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

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:

(comment: LPAR = Host-Name)

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:

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.

It works, somehow, until the point

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

There I get the error

Runtime-Error: 55 - File already opened.

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?

解决方案

Two things.

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"

Change all your Print #1 to 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.

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 -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屋！