FacebookTwitter

How to Connect to RDP from Excel VBA Macro

on Nov 6, 2013 in Blog | 8 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) *If you do not see the developer tab, make sure it is enabled. Browse to File > Options > Customize Ribbon > and ensure the “Developer” checkbox is checked.

Paul Chris Luke excel options enabled developer on ribbon

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.

 

8 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

  3. Earl

    September 25, 2014

    Post a Reply

    Dont know if you are still around, Chris, but good stuff. It’s the closest to what I am trying to do. I don’t want to see the remote desktop, I want to execute a .bat file that is on that desktop. I dont need to see anything. I will simply look for the results. Any ideas?

    • Chris

      September 25, 2014

      Post a Reply

      *edit2 After doing a little bit of research I found this article on stackexchange http://stackoverflow.com/questions/305035/how-to-use-ssh-to-run-shell-script-on-a-remote-machine with the following answer:

      If Machine A is a Windows box, you can use Plink (part of PuTTY) with the -m parameter, and it will execute the local script on the remote server.

      plink root@MachineB -m local_script.sh
      If Machine A is a Unix-based system, you can use:

      ssh root@MachineB ‘bash -s’ < local_script.sh
      You shouldn't have to copy the script to the remote server to run it.

      Are you wanting to run this command from excel? Or just get the script running via any means necessary?

      *edit1, I just realized you are wanting to run a bat file on the desktop you are going to be RDP’d into. Interesting! Are there credentials needed to access the machine?

      Hey Earl! I’m still alive and kickin ;) Try this code in your module:

      Sub bat()
      Shell “C:\Users\Chris Luke\Documents\bat.bat”
      End Sub

      *replace the directory I have listed here with the directory of your batch. In this case, “Shell” would be the command to open your file, then you simply insert your directory and it should run! Let me know if it works for you…

      -Chris

  4. Earl

    September 28, 2014

    Post a Reply

    Thanks for responding Chris. *Edit 2 is not an option due to downloading software. I use this line to open the Remote: TVx = Shell(“C:\windows\system32\mstsc.exe /v:” & “RemoteComputerName”, 1). I am running it in VBA Excel. I just need the extra code to run the xx.bat file on that desktop. Been searching and trying to figure this out for over a year. Most of the solutions require download of software. Thanks

    • Chris

      September 30, 2014

      Post a Reply

      Hmm. Without additional software I wouldn’t know how to achieve your goal, it’s beyond my skill level :( I apologize, I’ll keep my eyes out for any solutions in the future, just in case. Good luck on your continued quest for improved efficiency!

Leave a Reply