Differences

This shows you the differences between the selected revisions of the page.

library_vb 2012-03-21 library_vb 2022-10-21 (current)
Line 1: Line 1:
====== Using WinSCP .NET Assembly from Visual Basic for Applications (VBA) ====== ====== Using WinSCP .NET Assembly from Visual Basic for Applications (VBA) ======
-&beta_feature 
-===== Installing and Registering for COM =====+===== [[installing]] Installing and Registering for COM =====
First, you need to [[library_install|install the WinSCP .NET assembly and register it for COM]]. First, you need to [[library_install|install the WinSCP .NET assembly and register it for COM]].
-===== Using from VBA ===== +Check if your installation of Microsoft Office is 32-bit or 64-bit and [[library_install#registering|register]] the assembly accordingly.
-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//.+===== [[using]] Using from VBA ===== 
 +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. There are some less known techniques that you may need to use, which are described in following sections.
Line 15: Line 16:
Members of enumerations are represented as constants with name like ''<type>_<member>'', e.g. ''Protocol.Sftp'' becomes ''Protocol_Sftp''. Members of enumerations are represented as constants with name like ''<type>_<member>'', e.g. ''Protocol.Sftp'' becomes ''Protocol_Sftp''.
-==== Event Handlers ====+==== [[event_handlers]] Event Handlers ====
The ''[[library_session|Session]]'' class exposes several [[library_session#events|events]]. The ''[[library_session|Session]]'' class exposes several [[library_session#events|events]].
If you need to make use of these events: If you need to make use of these events:
  * Implement your interactions with WinSCP .NET assembly in a class module;   * Implement your interactions with WinSCP .NET assembly in a class module;
-  * Declare private variable in our class module referring to ''[[library_session|Session]]'' class; +  * Declare private variable in your class module referring to ''[[library_session|Session]]'' class; 
-  * Use ''[[http://msdn.microsoft.com/en-us/library/gg251653.aspx|WithEvents]]'' keyword, when declaring the private variable;+  * Use [[https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/private-statement|''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.   * Define private function (method) with name ''<variablename>_<event>'' and two arguments (e.g. ''sender'' and ''e'') for every event you need to handle.
Line 40: Line 41:
    MsgBox e.Filename & " => " & e.Destination     MsgBox e.Filename & " => " & e.Destination
End Sub End Sub
 +</code>
 +
 +==== [[error]] Error Handling ====
 +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 ''[[https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement|On Error]]'' statement.
 +
 +Use ''On Error Resume Next'' to disable default error handling. Then you need to query ''[[https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/err-object|Err.Number]]'' after every statement to test for errors. You can revert to default error handling (aborting the macro) using ''On Error GoTo 0''.
 +
 +<code vb>
 +' 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/")
 +</code>
 +
 +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 ''[[library_session_dispose|Session.Dispose]]'' is called even in case of error.
 +
 +<code vb>
 +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
 +...
</code> </code>
Line 52: Line 131:
    Dim mySession As New Session     Dim mySession As New Session
       
 +    ' Enable custom error handling
    On Error Resume Next     On Error Resume Next
-     
-    mySession.DisableVersionCheck = True 
       
    Upload mySession     Upload mySession
Line 61: Line 139:
    If Err.Number <> 0 Then     If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description         MsgBox "Error: " & Err.Description
 +
 +        ' Clear the error
 +        Err.Clear
    End If     End If
       
-    ' Clear the error 
-    Err.Clear 
-         
    ' Disconnect, clean up     ' Disconnect, clean up
    mySession.Dispose     mySession.Dispose
Line 83: Line 161:
        .UserName = "user"         .UserName = "user"
        .Password = "mypassword"         .Password = "mypassword"
-        .SshHostKey = "ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"+        .SshHostKeyFingerprint = "ssh-rsa 2048 xxxxxxxxxxx..."
    End With     End With
       
Line 94: Line 172:
       
    Dim transferResult As TransferOperationResult     Dim transferResult As TransferOperationResult
-    Set transferResult = mySession.PutFiles("d:\toupload\*", "/home/user/", False, myTransferOptions)+    Set transferResult =
 +········mySession.PutFiles("d:\toupload\*", "/home/user/", False, myTransferOptions)
       
    ' Throw on any error     ' Throw on any error

Last modified: by martin