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