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

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

Check if your installation of Microsoft Office is 32-bit or 64-bit and register the assembly accordingly.

You use WinSCP .NET assembly from VBA (Microsoft Office, including Excel, Word and Access) as any other COM library.

In Microsoft Visual Basic editor, go to Tools > References, check reference to WinSCP scripting interface .NET wrapper 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 your 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

VBA does not support catching exceptions, what is a common way of handling errors in examples for most other languages.

In case you need to use custom error handling, instead of interrupting a VB macro (the default behavior), use On Error statement.

Use On Error Resume Next to disable default error handling. Then you need to query Err.Number after every statement to test for errors. You can revert to default error handling (aborting the macro) using On Error GoTo 0.

' Enable custom error handling
On Error Resume Next
 
' Now, with custom error handling enabled,
' macro does not abort, when opening a session fails
mySession.Open sessionOptions
 
' Query for errors
If Err.Number <> 0 Then
    MsgBox "Error opening session: " & Err.Description
    End
End If
 
' Restore default error handling
On Error GoTo 0
 
' Now, with default error handling restored,
' macro aborts, if a reading remote directory fails
Dim directoryInfo As RemoteDirectoryInfo
Set directoryInfo = session.ListDirectory("/home/user/")

If you do not want to test for errors after every statement, you need to group the statements you want to guard into a subprocedure and enable custom error handling before calling/entering the subprocedure.

This approach is also recommended to ensure that Session.Dispose is called even in case of error.

Sub ListDirectory(ByRef mySession As Session)
    ' Setup session options
    ...
 
    ' Connect
    mySession.Open sessionOptions
 
    Dim directoryInfo As RemoteDirectoryInfo
    Set directoryInfo = session.ListDirectory("/home/user/")
 
    ' Do some stuff with directory listing
    ...
End Sub
 
Dim mySession As New Session
 
' Enable custom error handling
On Error Resume Next
 
' Now, with custom error handling enabled before calling the ListDirectory subprocedure,
' any statement in the subprocedure terminates the subprocedure (but not the whole execution)
ListDirectory mySession 
 
' Query for errors
If Err.Number <> 0 Then
    MsgBox "Listing directory failed: " & Err.Description
 
    ' Disconnect, clean up
    session.Dispose
 
    End
End If
 
' Disconnect, clean up
mySession.Dispose
 
' Restore default error handling
On Error GoTo 0
 
' Now with session cleanly closed, safely do anything unrelated to WinSCP session
...

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
    
    ' Enable custom error handling
    On Error Resume Next
    
    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 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"
        .SshHostKeyFingerprint = "ssh-rsa 2048 xxxxxxxxxxx..."
    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