I want to DOWNLOAD files using FTP within Excel using VBA.
I'm getting a "Connection Failed" error.
Any ideas what I am doing wrong?
(I am using FTP because I don't want to use Sftp which requires a SshHostKeyFingerprint.)
Sub DownloadFile()
Dim mySession As New session
' Enable custom error handling
On Error Resume Next
Download mySession
' Upload mySession
' Query for errors
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Description
' Clear the error
Err.Clear
End If
' Disconnect, clean up
mySession.Dispose
' Restore default error handling
On Error GoTo 0
End Sub
'
Private Sub Download(ByRef mySession As session)
FTPServer = "ftp://host2.bakop.com/" ' = Range("K6")
FTPFolder = "STS Data" ' = Range("K7")
FTPUsername = "utility" ' = Range("K8")
FTPPassword = "Rosebud1" ' = Range("K9")
' LocalFilename = Range("K10")
LocalFolder = ActiveWorkbook.Path
' Setup session options
Dim mySessionOptions As New sessionOptions
With mySessionOptions
.Protocol = Protocol_Ftp
.HostName = FTPServer
.UserName = FTPUsername
.Password = FTPPassword
' .SshHostKeyFingerprint = "X"
End With
' Connect
mySession.Open mySessionOptions
' Upload files
Dim myTransferOptions As New transferOptions
myTransferOptions.TransferMode = TransferMode_Binary
Dim transferResult As TransferOperationResult
Set transferResult = _
mySession.GetFiles("/" & FTPFolder & "/", LocalFolder, False, myTransferOptions)
' mySession.GetFiles("/" & FTPFolder & "/", LocalFolder & "\" & LocalFilename, False, myTransferOptions)
' Throw on any error
transferResult.Check
' Display results
Dim transfer As TransferEventArgs
For Each transfer In transferResult.Transfers
MsgBox "Download of " & transfer.Filename & " succeeded"
Next
End Sub