Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

martin

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?
Narna

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