VBA FTP DOWNLOAD EXCEL

Advertisement

Narna
Joined:
Posts:
1
Location:
South Africa

VBA FTP DOWNLOAD EXCEL

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

Reply with quote E-mail

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,276
Location:
Prague, Czechia

Re: VBA FTP DOWNLOAD EXCEL

A hostname is "host2.bakop.com", not "ftp://host2.bakop.com/".

(I am using FTP because I don't want to use Sftp which requires a SshHostKeyFingerprint.)
What's pretty lame excuse. What wrong with SshHostKeyFingerprint?

Reply with quote

Advertisement

You can post new topics in this forum