Forum » .NET assembly / COM library »

Help using Session.FileTransferProgress Event within VBA module

Advertisement

tkpatchell
Joined:
Posts:
4
Location:
Canada

Help using Session.FileTransferProgress Event within VBA module

I am having trouble figuring out a way to use the FileTransferProgress to return a status of how the download is progressing.

I would like to be able to see somewhere (debug screen for now) how far along a download is as some of the files are taking a long time to download and I would like to know that things are still progressing. Due to my lack of knowledge of classes, powershell, VB.net and C#, i don't know how to translate the examples you provided into VBA, so I thought I would put it out here to see if someone can lend a hand.

Here's what I got so far: 
Sub wsDownload(myProtocol As Integer, myFTP As String, myUserName As String, myPassword As String, _
                        LocalDirectory As String, RemoteDirectory As String, Optional RemoteFile As String)
    
    Dim mySession As New Session
    Dim mySessionOptions As New SessionOptions
    Dim myTransferOptions As New TransferOptions
    Dim transferResult As TransferOperationResult
    Dim transfer As TransferEventArgs

    If RemoteFile = "" Then RemoteFile = "*.*"
    ' Setup session options
    
    With mySessionOptions
        .Protocol = myProtocol
        .HostName = myFTP
        .UserName = myUserName
        .Password = myPassword
        '.sshhostkeyfingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
    End With
    ' Connect
    mySession.Open mySessionOptions
    
    ' Download files
    
    myTransferOptions.TransferMode = TransferMode_Binary
     
    
    Set transferResult = mySession.GetFiles(RemoteDirectory & RemoteFile, LocalDirectory)
     
    ' Throw on any error
    transferResult.Check
     
    ' Display results
    
    For Each transfer In transferResult.Transfers
        'MsgBox "Download of " & transfer.FileName & " succeeded" & "," & transfer.destination
    Next
    ' Disconnect, clean up
    mySession.Dispose
End Sub

The code works great, but on the line: 
    Set transferResult = mySession.GetFiles(RemoteDirectory & RemoteFile, LocalDirectory)
the code 'freezes' there until all the downloads are finished (I am downloading around 38 files totalling around 1.5GB).

How do I enhance this to print (debug.print) the percent completion of a download?

Thanks,

Tom

Reply with quote

Advertisement

tkpatchell
Joined:
Posts:
4
Location:
Canada

Thanks for replying.

I'll chock it up to inexperience, but I just don't understand how to get it to work. I don't understand Powershell at all. My challenge may be that i need to use classes in my VBA project, but i'm unfamiliar with how to implement them.

VB.Net is the most similar to VBA, but the line: 
AddHandler mySession.FileTransferProgress, AddressOf SessionFileTransferProgress

has no equivalent in VBA. My attempts at translating the examples from C#, VBnet and powershell didn't get me anywhere

If someone has some experience using FileTransferProgress in VBA during a GetFiles and PutFiles call, I would apprciate some feedback

The dream would be that during a GetFiles batch, I would be able to visually display the progress of a transfer with a custom bar, based on the FileTransferProgressEventArgs class

Thanks
TOm

Reply with quote

tkpatchell
Joined:
Posts:
4
Location:
Canada

martin wrote:

Sorry, I somehow thought you are using PowerShell.

For VBA, see:
https://winscp.net/eng/docs/library_vb#event_handlers

I finally figured this out. It came down to the fact that I have never worked with Classes before. I don't know how it works, but it does.

I thought I would post my solution, should there be anyone else that might be having trouble

In VBA, I created a new class, called 'cWinSCP'
Within that class I have: 
Private WithEvents mySession As Session
Private myForm As Form_frm_winSCP_Progress
Private Sub mySession_FileTransferred(ByVal sender, ByVal e As TransferEventArgs)
    Debug.Print e.FileName & " => " & e.Destination
    'Use e.FileName and e.Destination on my Transfer Result Form
End Sub
Private Sub mySession_FileTransferProgress(ByVal sender, ByVal e As FileTransferProgressEventArgs)
    'Debug.Print e.FileName & ", e.Directory: " & e.Directory & ", e.Operation: " & e.Operation & ", ProgressSide: " & e.Side
    'Events:
    'e.FileName:        File name
    'e.CPS:             Transfer rate
    'e.Directory:       Source of file i think
    'e.Operation:       Can only be 0 - useless right now
    'e.FileProgress:    File progress, from 0-1
    'e.OverallProgress: total progress, from 0-1
    'e.ProgressSide:    0 = download, 1 = upload
    With Form_frm_winSCP_Progress
        If e.FileProgress > 0 Then
            .lbl_Progress.Caption = FormatPercent(e.FileProgress, 0)
        Else
            .lbl_Progress.Caption = "0%"
        End If
        If e.OverallProgress > 0 Then
            .lbl_TotalProgress.Caption = FormatPercent(e.OverallProgress, 0)
        Else
            .lbl_TotalProgress.Caption = "0%"
        End If
        .lbl_FileName.Caption = e.FileName
        .lbl_FileSize.Caption = "unsure"
        .lbl_SourceFolder.Caption = e.Directory
        .lbl_DestinationFolder.Caption = e.Directory
    End With
    'SysCmd acSysCmdInitMeter, e.FileName & " (" & Format(Round(e.CPS / 1024, 0), "###,###.0 KB/S)"), 100
    'SysCmd acSysCmdUpdateMeter, e.FileProgress * 100
End Sub
Sub wsDownload(myProtocol As Integer, myFTP As String, myUserName As String, myPassword As String, _
                        LocalDirectory As String, RemoteDirectory As String, Optional remotefile As String)
    
    Set mySession = New WinSCPnet.Session
    Dim mySessionOptions As New WinSCPnet.SessionOptions
    Dim myTransferOptions As New WinSCPnet.TransferOptions
    Dim transferResult As WinSCPnet.TransferOperationResult
    Dim transfer As WinSCPnet.TransferEventArgs

    If remotefile = "" Then remotefile = "*.*"
    If Right(LocalDirectory, 1) <> "\" Then
        LocalDirectory = LocalDirectory & "\"
    End If
    If Right(RemoteDirectory, 1) <> "/" Then
        RemoteDirectory = RemoteDirectory & "/"
    End If
    
    ' Setup session options
    With mySessionOptions
        .Protocol = myProtocol
        .HostName = myFTP
        .UserName = myUserName
        .Password = myPassword
        '.sshhostkeyfingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
    End With
    ' Connect
    mySession.Open mySessionOptions
    
    ' Download files
    
    myTransferOptions.TransferMode = TransferMode_Binary
    
    DoCmd.OpenForm "frm_WinSCP_Progress"
    With Form_frm_winSCP_Progress
        .lbl_SourceFolder.Caption = RemoteDirectory
        
    End With
    Set transferResult = mySession.GetFiles(RemoteDirectory & remotefile, LocalDirectory)
     
    ' Throw on any error
    transferResult.Check
     
    ' Display results
    
    For Each transfer In transferResult.Transfers
        'MsgBox "Download of " & transfer.FileName & " succeeded" & "," & transfer.destination
    Next
    ' Disconnect, clean up
    mySession.Dispose
End Sub

In a module, i then call the custom winscp routine wsDownload by: 

Dim myWinSCP As New cWinSCP
Call myWinSCP.wsDownload(download paramaters)

I use the data in FileTransferProgress to update the form with the pertinent information. I also tried to update the status bar on the bottom of the Access window, but that doesn't seem to refresh correctly and isn't working

Thanks,
Tom

Reply with quote

Advertisement

You can post new topics in this forum

Documentation

Support

Associations

Follow Us