library_vb » Revisions »
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 | ||