How to connect to Linux server from Microsoft Excel

[vyshakhv]

Generally, we kept all our inventory data in Microsoft excel such as server IP, hostname & configuration etc. We generally do copy IP address from this excel, so that it can be pasted in putty.exe for connection. But what if don’t need to copy & paste in details in excel, instead you just need to double-click on the cell which has an IP address for connection with the server. This post will guide you about how to connect to Linux server from Microsoft Excel.

Pre-requisite to connect to Linux server from Microsoft Excel

1. Inventory with write access
2. putty.exe

How to connect to Linux server from Microsoft Excel

1. Download the putty.

Please download putty.exe from the below links as per your architecture (32 Bit or 64 Bit)

putty.exe (the SSH and Telnet client itself)
32-bit: putty.exe https://the.earth.li/~sgtatham/putty/latest/w32/putty.exe
64-bit: putty.exe https://the.earth.li/~sgtatham/putty/latest/w64/putty.exe
Please keep the downloaded putty.exe under c:\ location.

2. Open the Excel File.

Now open the Microsoft excel ie your inventory where you have kept all your server details

3. Write Macro for automating the task.

Now we need to write sample macro so that it will call putty.exe upon double click event. For that Press “Alt+F11” within Microsoft Excel.
Now Right click on Sheet1 (sheet in which you have all your IP details) and select View code option
Now in the blank window on the right side paste below code, so that whenever you double-click on IP column, putty.exe will be called with passing IP as a parameter.

Code: [Select]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Shell "C:\putty.exe -ssh " & ActiveCell, 1
    Cancel = True
End Sub
in case your username is same for all your connection use below code so that you only need to type the password in the putty window

Code: [Select]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Shell "C:\putty.exe -ssh -l root " & ActiveCell, 1
    Cancel = True
End Sub
In above we have used root as a user for connecting automatically using putty.

4. Save the file with .xltm extension.

Now Press Ctrl+s for saving the file. Save this file as “Excel Macro-Enabled Template” with “.xltm” extension.

5. Double click on IP column for connection with the server.

Now you are almost done to connect to Linux server from Microsoft Excel. Just double-click on the cell which has IP and putty will be called automatically passing IP as a parameter.