Differences

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

library_vb 2012-03-22 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 45: Line 46:
VBA does not support catching exceptions, what is a common way of handling errors in examples for most other languages. 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 behaviour), use ''[[http://msdn.microsoft.com/en-us/library/gg251688.aspx|On Error]]'' statement.+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 ''[[http://msdn.microsoft.com/en-us/library/gg251525.aspx|Err.Number]]'' after every statement to test for errors. You can revert to default error handling (aborting the macro) using ''On Error GoTo 0''.+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> <code vb>
Line 54: Line 55:
' Now, with custom error handling enabled, ' Now, with custom error handling enabled,
-' script does not abort, when opening session fails+' macro does not abort, when opening a session fails
mySession.Open sessionOptions mySession.Open sessionOptions
Line 67: Line 68:
' Now, with default error handling restored, ' Now, with default error handling restored,
-' script aborts, if reading remote directory fails+' macro aborts, if a reading remote directory fails
Dim directoryInfo As RemoteDirectoryInfo Dim directoryInfo As RemoteDirectoryInfo
Set directoryInfo = session.ListDirectory("/home/user/") Set directoryInfo = session.ListDirectory("/home/user/")
</code> </code>
-If you do not want to test for errors after every statement, you need to group the staments you want to guard into a subprocedure and enable custom error handling before calling/entering the subprocedure.+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. This approach is also recommended to ensure that ''[[library_session_dispose|Session.Dispose]]'' is called even in case of error.
Line 160: 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 171: 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