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

2 comments:

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

    ReplyDelete
  2. Changing to a VPS Hosting (Virtual Dedicated Server) has horrendous ramifications just as its imperative to be acquainted with the highlights and advantages of VPS so your venture can reimburse you with rich profits which you makes on Hosting of your sites. buy vps with paypal

    ReplyDelete