Using Rebex SFTP from Excel VBA project

Question

Does Rebex support VBA Excel project? If it does, could you provide any example, how to connect sftp in VBA application?

Answer 1

Rebex components cannot be called directly from VBA – the components does not have a COM interface.

However, you can write a simple wrapper class, providing some operations using Rebex components.

Here is a brief example:

1) Add the following Rebex libraries to Global Assembly Cache (GAC)

Rebex.Common.dll
Rebex.Networking.dll
Rebex.Sftp.dll

I recommend to use the .NET 2.0 versions of the libraries. The libraries can be found in folder they have been installed, e.g. c:\Program Files (x86)\Rebex Components 2012 R3\bin\net-2.0\

2) Create a class-library project in Visual Studio. Let’s name it “SftpWrapper”.

3) Add references to the Rebex libraries mentioned in previous step.

4) Modify the project properties to make the class library “COM-visible”:
On the ‘Application’ tab click the ‘Assembly Information…’ button.
In the resulting dialog check the ‘Make assembly COM-visible’ checkbox.
Click ‘OK’.

5) Enforce the Visual Studio to register this assembly as COM:
On the ‘Build’ tab of Project properties, check the ‘Register for COM interop’ checkbox (towards the bottom: you may need to scroll down).

6) Create a public class (named e.g. “SftpTransfer”) within this project and add a following sample method there:

public string DownloadFileToString(string hostname, int port, username, string password, string remoteFilename)    
{     
  var client = new Sftp();
  client.Connect(hostname, port);
  client.Login(username, password);
  string buf;
  using (var ms = new MemoryStream())
  {
    client.GetFile(remoteFilename, ms);
    ms.Seek(0, SeekOrigin.Begin);
    using (var sr = new StreamReader(ms))
    {
      buf = sr.ReadToEnd();
    }
  }
  client.Disconnect();

  return buf;
}

7) Build the assembly and in the “Output window” of Visual Studio make sure there is no error when registering the assembly as COM.

8) Start Excel and open it’s VBA editor.
Select ‘References’ on the Visual Basic Editor’s ‘Tools’ menu.
If you scroll down in the resulting dialog you should find that ‘SftpWrapper’ is in the list.
Check the checkbox alongside it and click ‘OK’.

9) Now you can write a VBA function, that calls the wrapper method.
The function can look like this:

Function GetFile()
  Dim transfer As New SftpWrapper.SftpTransfer
  GetFile = transfer.DownloadFileToString("hostname", 22, "username", "password", "Incoming/file1.txt")
End Function

For more information about calling .NET libraries from Excel VBA, please read this article.