- Installing and Registering for COM
- Using from VBA
First, you need to install the WinSCP .NET assembly and register it for COM.
Note that Microsoft Office applications are 32-bit, so you need to register the assembly for 32-bit .NET framework, even on 64-bit systems.
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
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
WithEventskeyword, when declaring the private variable;
- Define private function (method) with name
<variablename>_<event>and two arguments (e.g.
e) for every event you need to handle.
Following example shows how to handle a
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.
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 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