Using FTP in VBA

Question

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?

Answer 1

You're on the right path and following Jean-François's answer should get you there.

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.

But, if you're limited to strict VBA, then this is the way to go:

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

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.