SSH Client using VBA
VBA as a language used in MS Office applications enables you to automate some of the Excel functions such as graphical presentation of data analysis, data reporting, organization and formatting. To use Excel VBA, a command line window needs to be run.
SSH client and secure file transfer
On a day-to-day basis, it becomes necessary to upload/download files from/to the remote server. First of all, access to the server is need. It can be set up through FTP and SSH protocols by using such programs as PuTTY and WinSCP. It will allow you to remotely connect a server to a PC and transfer files within the established network.
Let's have a look at the example of using simple SSH client for uploading and downloading files based on Excel. It is possible to modify it to ease the automation of tasks on the remote server.
To access the server, we need to know the following:
- IP address
- SSH port (22 by default)
Graphical example can be found on a picture below:
As it can be seen from above, there are three main functions used in the program: connection to the server, uploading and downloading files to/from the server
How to connect to SFTP
SFTP (Secure File Transfer Protocol) is similar to SSH protocol. However, it enables secure connection for transferring files not only remotely but locally as well. As the SSH protocol, SFTP has the same method of authentication. You will need to know:
- Host name of the server;
- Protocol of the server (SFTP, FTP, etc.);
Enter the data in the login dialog window to connect to SFTP.
How to upload files through SFTP
Before you start uploading files, make sure that your user account is connected to the SFTP server. To upload/download files, you might do the following: drag and drop icons (files) from one PC file system to another one; use copy/paste or choose an option "Send to" in a context menu.
Any changes relating SFTP settings can be made in the SFTP commander interface.
Calling PuTTY from Excel VBA
PuTTY which is a file transfer application can be opened manually or automatically depending on settings. To start PuTTY session from VBA, you will need a VBA command line. To open current VBA session in PuTTY, it is needed to enter following details in the login window:
- Host name you are connecting to;
- And press "Start PuTTY".
All settings can be found in PuTTY configuration window