Using WinSCP .NET Assembly from Visual Basic for Applications (VBA)

First, you need to install the WinSCP .NET assembly and register it for COM.

You use WinSCP .NET assembly from VBA as any other COM library.

In Microsoft Visual Basic editor, go to Tools > References, check reference to .NET wrapper for WinSCP console interface and confirm with OK.

There are some less known techniques that you may need to use, which are described in following sections.

Members of enumerations are represented as constants with name like <type>_<member>, e.g. Protocol.Sftp becomes Protocol_Sftp.

The Session class exposes several events.

If you need to make use of these events:

• Implement your interactions with WinSCP .NET assembly in a class module;
• Declare private variable in our class module referring to Session class;
• Use WithEvents keyword, when declaring the private variable;
• Define private function (method) with name <variablename>_<event> and two arguments (e.g. sender and e) for every event you need to handle.

Following example shows how to handle a Session.FileTransferred event.

Private WithEvents mySession As Session
 
Public Sub Example()
    Set mySession = New Session
    ' Open connection
    ...
    ' Upload files
    ...
End Sub
 
Private Sub mySession_FileTransferred(ByVal sender, ByVal e As TransferEventArgs)
    MsgBox e.Filename & " => " & e.Destination
End Sub

This example VBA module (not a class module) is functionally equivalent to overall C# example for WinSCP .NET assembly.

Option Explicit
 
Sub Example()
 
    Dim mySession As New Session
    
    On Error Resume Next
    
    mySession.DisableVersionCheck = True
    
    Upload mySession
    
    ' Query for errors
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description
    End If
     
    ' Clear the error
    Err.Clear
        
    ' Disconnect, clean up
    mySession.Dispose
     
    ' Restore default error handling
    On Error GoTo 0
    
End Sub
 
Private Sub Upload(ByRef mySession As Session)
 
    ' Setup session options
    Dim mySessionOptions As New SessionOptions
    With mySessionOptions
        .Protocol = Protocol_Sftp
        .HostName = "example.com"
        .UserName = "user"
        .Password = "mypassword"
        .SshHostKey = "ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
    End With
    
    ' Connect
    mySession.Open mySessionOptions
    
    ' Upload files
    Dim myTransferOptions As New TransferOptions
    myTransferOptions.TransferMode = TransferMode_Binary
     
    Dim transferResult As TransferOperationResult
    Set transferResult = mySession.PutFiles("d:\toupload\*", "/home/user/", False, myTransferOptions)
     
    ' Throw on any error
    transferResult.Check
     
    ' Display results
    Dim transfer As TransferEventArgs
    For Each transfer In transferResult.Transfers
        MsgBox "Upload of " & transfer.Filename & " succeeded"
    Next
    
End Sub