FacebookTwitter

How to Connect to RDP from Excel VBA Macro

on Nov 6, 2013 in Blog | 4 comments

Share On GoogleShare On FacebookShare On Twitter

Let me be the first to say that I don’t expect anyone to read this post. I am merely using this venue as a way to make my own personal notes on how I solved a huge frustration for me doing a task at work which required logging into multiple machines per day. If by some chance you found this content and it helped you or you have questions, leave a comment!

Problem: I have to log into multiple hosts using rdp (mstc /admin) per day. Clicking start > Run > mstsc /admin [hostname] took forever. I would type the commands, copy the hostname, paste… overall that simple process took around 15 seconds. Now, 15 seconds does not seem like a lot of time at first. However, connect to 100 hosts today and you’ve spent 25 minutes simply connecting to machines. Thats around 100 hours per year wasted. Having a button which is able to rdp from excel with one click would be much better!

Solution:

1. Make your document macro enabled. File > Save As >Save as type: Excel Macro-Enabled Workbook (*.xlsm)

2. Click on developer tab in the top ribbon (farthest to the right)

3. Click on Visual Basic (this should load a new window for coding)

4. Click on Insert (“I”) and select new module

5. Copy this code:

Sub hostname()

RDPWindow = Shell(“C:\windows\system32\mstsc.exe /admin /v:” & “hostname“, 1)

End Sub

* replace hostname with the name of your host.

6. Paste the code in your new Module.

7. Save in Visual Basic Editor, and save your workbook.

8. Still in the Developer Tab, click on Insert (an icon with hammer and wrench on it)

9. In the drop down menu that appears, under the Form Controls section, select the button icon (top left)

10. Draw your button

11. Right click your new button, select assign macro

12. Select the new macro you just created, press ok

13. If desired rename your button to “connect to hostname

14. Save and test.

15. Do a happy dance!

As always, input is gladly welcomed! Love you guys, hopefully this may help someone out there save some time.

 

4 Comments

  1. Surya

    December 24, 2013

    Post a Reply

    Hello Paul,
    Thanks for this tutorial on connecting to a RDP via VBA. It was useful.
    I wanted to go little ahead and pass in the credentials to be able to login automatically. I’m a complete beginner when it comes to VB, so have no idea of these things.

    thanks
    Surya

    • Chris

      December 24, 2013

      Post a Reply

      Happy to help! Were you able to figure out how to add credentials to the macro login? I currently don’t do that, as it poses a security risk for my purposes… I’m sure I could figure it out and help you if needed though!

  2. Chris

    April 24, 2014

    Post a Reply

    Sub Connect()
    Dim Test As String
    Selection.End(xlToLeft).Select
    Test = ActiveCell.Value
    If InStr(Test, “SWT”) Then
    PUTTY
    Else
    MSTSC Test
    End If

    End Sub
    Sub MSTSC(Server As String)
    Dim retVal As Variant
    retVal = Shell(“c:\windows\system32\mstsc.exe /v:” & ActiveCell.Value, vbNormalFocus)
    End Sub
    Sub PUTTY()
    Dim Server As String
    Server = ActiveCell.Offset(0, 1).Value
    MsgBox (“putty -ssh ” & Server)
    End Sub

    This works for me where column a is the server name and column b the IP.
    If the name contains SWT then login using putty via SSH

    Hope this helps you

Leave a Reply