Get a file via ftp using VBA

' Edit these variables to match your specifications
ftp_address          = "hosted.datascope.reuters.com"
ftp_username         = "6666666"
ftp_password         = "xxxxxxxxx"
ftp_remote_directory = "reports" ' Leave blank if uploading to root directory
ftp_files_to_put     = "abcdefg.xml"     ' You can use wildcards here (e.g. *.txt)

'On Error Resume Next
Set oScript = CreateObject("WSCRIPT.SHELL")
Set oFileSys = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Build our ftp-commands file
Set objTextFile = objFSO.CreateTextFile("test.ftp")
objTextFile.WriteLine "lcd ."
objTextFile.WriteLine "open " & ftp_address
objTextFile.WriteLine ftp_username
objTextFile.WriteLine ftp_password

' Check to see if we need to issue a 'cd' command
If ftp_remote_directory <> "" Then
   objTextFile.WriteLine "cd " & ftp_remote_directory
End If

objTextFile.WriteLine "prompt"

' If the file(s) is/are binary (i.e. .jpg, .mdb, etc..), uncomment the following line' objTextFile.WriteLine "binary"
' If there are multiple files to put, we need to use the command 'mput', instead of 'put'
If Instr(1, ftp_files_to_put, "*",1) Then
   objTextFile.WriteLine "mput " & ftp_files_to_put
Else
   objTextFile.WriteLine "get " & ftp_files_to_put
End If
objTextFile.WriteLine "bye"
objTextFile.Close
Set objTextFile = Nothing
' Use cmd.exe to run ftp.exe, parsing our newly created command file
strCMD = "ftp.exe -s:test.ftp"

'strTempFile = "C:\" & oFileSys.GetTempName( )
strTempFile = oFileSys.GetTempName( )

' Pipe output from cmd.exe to a temporary file (Not :| Steve)
Call oScript.Run ("cmd.exe /c " & strCMD & " > " & strTempFile, 0, True)

Set oFile = oFileSys.OpenTextFile (strTempFile, 1, False, 0)

On Error Resume Next
' Grab output from temporary file
strCommandResult = oFile.ReadAll
oFile.Close
' Delete the temporary & ftp-command files
Call oFileSys.DeleteFile( strTempFile, True )
Call objFSO.DeleteFile("test.ftp", True )
Set oFileSys = Nothing
Set objFSO = Nothing
' Print result of FTP session to screen