Friday, August 6, 2010

Using Excel to double click to RDP to a server

I regret that it's been a while since my last post.  I started a new job, and it's kept me busy! In my new role I manage many SQL Servers.  For years I've used mstsc.exe from a shell to connect to an RDP session.  Since the customer lacks a CMDB I created a crude one in Excel.  I wanted some functionality to save time when I'm working down the list of servers.  So I looked into how I could make Excel a launch point for my RDP sessions.
Example Spreadsheet
Here's how it works.  Cell A has the hostname, Cell B has a Wingding with a colon character (which looks like a computer in Windows).  I bound some simple code to the Worksheet.BeforeDoubleClick Event.  It checks if the selected cell is in column B and then uses the range to find the value of the corresponding row's A column.  If the A column value is not an empty string it calls a function called launchRDP.  Note: I used column B instead of column A so that I could retain the default double click functionality in column A.  You could modify the code to work with column A but you might not like the end result.  

The code for the event driven macro is below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    currentCell = Replace(Target.Address, "$", "")
    hostName = Range("A" & Right(currentCell, Len(currentCell) - 1)).Value
    If (Left(currentCell, 1) = "B") Then
        If (hostName <> "") Then
            Call launchRDP(hostName)
        End If
    End If
End Sub

This code for launchRDP is below (put this in a new module):

Sub launchRDP(serverName)

    If (serverName <> "") Then
        RDPWindow = Shell("C:\windows\system32\mstsc.exe /w:950 /h:900 /v:" & serverName, 1)
    End If
End Sub

1 comment:

  1. are not very practical to excel but you can see an example of escel compiled this?