Uploading and Downloading files from FTP client using Excel

Question

I am having trouble getting the code below to work (from an answer here Using FTP in VBA). I'm in an FTP client from WinSCP and I'm not 100% sure how to use the code. When I run the macro, I get the successful MsgBox "Sent" popup, and I see that a file named whatever I put as FTP_BATCH_FILE_NAME gets created holding the following info:

"open " & FTP_ADDRESS
FTP_USERID
FTP_PASSWORD
"mput " & sWorkingDirectory & sFileToSend
quit

The person who posted the code noted that -

"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

But when I do step through the code, and after the yellow highlight passes this line in Excel, I enter this line in and I get an error that '?', sWorkingDirectory, and FTP_BATCH_FILE_NAME is not recognized as an internal or external command.

I thought that after typing it at Cmd line I'd be able to get the contents of FTP_BATCH_FILE_NAME into sFileToSend, but nothing is put into sFileToSend.

Option Explicit
Const FTP_ADDRESS = myHostAddr
Const FTP_USERID = myUsername
Const FTP_PASSWORD = myPassword

Sub Macro1()
    If Not SendFtpFile_F() Then
        MsgBox "Could not (send/get?) 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 = "a file in FTP I want to get.ftp"
    Const INCREASED_BUFFER_SIZE = 20480

    SendFtpFile_F = False

    sWorkingDirectory = "C:\Users\...\Documents\"
    sFileToSend = "test.txt" 'is this the file I want to put it into?

    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

Uploading and Downloading files from FTP client using Excel

0 Answers0