FacebookTwitter

How to Connect to RDP from Excel VBA Macro

on Nov 6, 2013 in Blog | 20 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.

*edit 10/11/15

Lots of comments on this post have been asking about inputting credentials automatically. Thomas, one of our commenters below provided this solution, hopefully it helps!:

Module1 code:
**********************************************************************************
Const RemoteDestop = “c:\windows\system32\mstsc.exe /v:”
Sub Connect_to_RDP(servername As String)
Dim RetVal As Variant
RetVal = Shell(RemoteDestop & servername)
End Sub

**********************************************************************************

Sheet with a list object code:

**********************************************************************************
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Selection, Me.ListObjects(1).ListColumns(1).Range, Me.ListObjects(1).DataBodyRange) Is Nothing Then
Module1.Connect_to_RDP Target.Value
Application.WindowState = xlMinimized
End If
End Sub

**********************************************************************************

Sheet has a single list where servername is in ListColumn(1)

 

20 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

  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!

  5. Thomas

    October 4, 2015

    Post a Reply

    I have been able to “save passwords” using the “keep credentials” checkbox in the RDP connection dialog. this requires a manual connection once then its automatic via script
    I also put all those guys in a listobject with a descriptor to the side of the IP/ server name, and any other categorical information to drill/ filter to the correct server.
    I further added code to the sheet to act as a double click handler and test which row was clicked to then send that row to the shell call function with the appropriate prefix…

    Don’t have it on my but i can post the code on request

    • Chris

      October 5, 2015

      Post a Reply

      That’s awesome man. This post actually gets a remarkable amount of traffic (around 100 views per day… everything else maybe gets 20 per month). I’m sure someone might find it helpful if you posted your work. I could edit the post as well to include it and credit you.

      • Thomas

        October 11, 2015

        Post a Reply

        Module1 code:
        **********************************************************************************
        Const RemoteDestop = “c:\windows\system32\mstsc.exe /v:”
        Sub Connect_to_RDP(servername As String)
        Dim RetVal As Variant
        RetVal = Shell(RemoteDestop & servername)
        End Sub

        **********************************************************************************

        Sheet with a list object code:

        **********************************************************************************
        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Selection, Me.ListObjects(1).ListColumns(1).Range, Me.ListObjects(1).DataBodyRange) Is Nothing Then
        Module1.Connect_to_RDP Target.Value
        Application.WindowState = xlMinimized
        End If
        End Sub

        **********************************************************************************

        Sheet has a single list where servername is in ListColumn(1)

        • Chris

          October 11, 2015

          Post a Reply

          I appreciate it Thomas! I added your code to the post, hopefully it helps others!

  6. Preethy Jana

    October 6, 2015

    Post a Reply

    hey, i am developing a macro where in i need to copy data from one remote desktop to another remote desktop. the first remote will only have database connection and the second remote will have excel wherein my macro will be running and pasting the data which is getting retrieved from first remote desktop. is it possible to do that??

    • robertocm

      May 1, 2017

      Post a Reply

      Let my just share some ideas

      Regarding to this: “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.”

      I would try this approach:
      1. Create a shortcut to the .bat on the remote desktop and assign hot keys. For example assign CTRL + ALT + E
      2. If the remote desktop window is not maximized it might be possible to send keystrokes from local VBA (not tested, just an idea for now). See examples below.

      And in reference to this: “i am developing a macro where in i need to copy data from one remote desktop to another remote desktop. the first remote will only have database connection and the second remote will have excel”
      I would try this approach:
      1. If there is at least one shared folder, accessible from both machines …
      2. In the remote desktop we can have a compiled script monitoring changes of a text file in the shared folder (i’m thinking in AutoIt using _WinAPI_FindFirstChangeNotification function, see an example at the end of this comment)
      3. Local excel can write to the text file in the shared folder (writing like a “command line”)
      4. Detecting the change in the file, remote script can execute an ADO query and save the recordset as an xml file or even INSERT INTO a table in a database file located in the shared folder.
      Something like this:

      $oADOConnection.Execute(“DROP TABLE MyTable”)
      $oADOConnection.Execute(“CREATE TABLE MyTable (Field1 int, Field2 VARCHAR(20), Field3 Int)”)
      ;$sADOSQL = “INSERT INTO MyTable (Field1, Field2, Field3) SELECT MyTable2.Field1, MyTable2.Field2, MyTable2.Field3 FROM [;Database=” & $sFilename2 & “;PWD=12345].MyTable2″
      $oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType) ; Issue the SQL query

      5. Local excel can use ADO to read the xml recorset or to query one access database in the shared folder

      Here two examples of sendkeys from VBA:

      Sub test_1()
      Dim Pausa As Long, Inicio As Long
      Pausa = 0.1

      ‘sintaxis adapted from these references:
      ‘http://www.vbforums.com/showthread.php?747425-SendKeys-and-Windows-8
      ‘http://www.vbforums.com/showthread.php?745925-RESOLVED-How-to-trigger-the-desktop-context-menu&p=4570289&viewfull=1#post4570289
      CreateObject(“WScript.Shell”).SendKeys “%O”, True
      Inicio = Timer
      Do While Timer < Inicio + Pausa
      DoEvents
      Loop
      CreateObject("WScript.Shell").SendKeys "C", True

      End Sub

      Sub test_2()
      Dim Pausa As Long, Inicio As Long
      Pausa = 0.1

      'Another alternative is using MySendKeys UDF by Karl E. Peterson (awesome I think)
      '"provides a drop-in replacement for the standard SendKeys statement, and should work just fine in all the environments (VB5, VB6 IDE, VBA other than Office 2007) where this broke"
      'http://vb.mvps.org/samples/SendInput
      'Important: read the license terms in the UDF code module
      MySendKeys "%O"
      Inicio = Timer
      Do While Timer < Inicio + Pausa
      DoEvents
      Loop
      MySendKeys "C"

      End Sub

      Example of AutoIt _WinAPI_FindFirstChangeNotification:

      #pragma compile(AutoItExecuteAllowed, True)

      #include

      Global Const $g_sPath = “__Here_the_path_of_share_folder_to_monitor”

      Global $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)

      Global $sFileRead
      Local $aArray

      Local $iID, $bBoolean = False

      While 1
      Sleep(1000)
      $iID = _WinAPI_WaitForSingleObject($hDir, 0)
      Switch $iID
      Case 0 ; WAIT_OBJECT_0
      If $bBoolean = True Then
      $bBoolean = False
      Else
      $bBoolean = True
      ;ConsoleWrite(‘A file was changed in the directory.’ & @CRLF)
      ReadLine()
      ;_RunAU3(“.\scripts\AU3_Example.au3″, $sFileRead, “”, @SW_SHOW, 8)
      ;$sFileRead with text like: AU3_Example.au3|parameter1 ‘parameter2′ “”parameter 3″”
      $aArray = StringSplit($sFileRead, “|”)
      _RunAU3(“.\scripts\” & $aArray[1], $aArray[2], “”, @SW_SHOW, 8)
      EndIf
      Case Else
      ContinueLoop
      EndSwitch
      If Not _WinAPI_FindNextChangeNotification($hDir) Then
      MsgBox(0, ‘Error’, ‘Unexpected error.’)
      Exit
      EndIf
      WEnd

      Func OnAutoItExit()
      _WinAPI_FindCloseChangeNotification($hDir)
      ;ConsoleWrite(‘Bye-bye!’ & @CRLF)
      EndFunc

      Func ReadLine()
      Local Const $sFilePath = $g_sPath & “\line.txt”
      ;Open the file for reading and store the handle to a variable.
      Local $hFileOpen = FileOpen($sFilePath, $FO_READ)
      If $hFileOpen = -1 Then
      MsgBox(0, “”, “An error occurred when reading the file.”)
      Return False
      EndIf

      ;Read the fist line of the file using the handle returned by FileOpen.
      $sFileRead = FileReadLine($hFileOpen, 1)

      ;Close the handle returned by FileOpen.
      FileClose($hFileOpen)
      ;Display the first line of the file.
      ;MsgBox($MB_SYSTEMMODAL, “”, $sFileRead)
      EndFunc

      ;https://www.autoitscript.com/forum/topic/135203-call-another-script/?do=findComment&comment=943199
      ;guinness
      Func _RunAU3($sFilePath, $sParamet=””, $sWorkingDir = “”, $iShowFlag = @SW_SHOW, $iOptFlag = 0)
      Return Run(‘”‘ & @AutoItExe & ‘” /AutoIt3ExecuteScript “‘ & $sFilePath & ‘” “‘ & $sParamet & ‘”‘, $sWorkingDir, $iShowFlag, $iOptFlag)
      ;Return Run(‘”‘ & @AutoItExe & ‘” /AutoIt3ExecuteScript “‘ & $sFilePath & ‘”‘ & ‘ “Bos Días”‘, $sWorkingDir, $iShowFlag, $iOptFlag)
      EndFunc ;==>_RunAU3

  7. moad ghanmi

    June 14, 2016

    Post a Reply

    thanks, but how can i connect to ssh from Excel, please could you please help me. :)

  8. robertocm

    May 4, 2017

    Post a Reply

    Update:
    Regarding to this: “I just need the extra code to run the xx.bat file on that desktop…”

    Kindly suggest reading this:
    http://stackoverflow.com/questions/24612397/rdp-session-launch-applications/43767813#43767813

    Tested:
    – Is it possible to send mouse clicks (and also text) to a Microsoft RDP Client Control embeded in an AutoIt GUI as described in the previou link (tested)
    – Then you can run a .bat file on the remote desktop sending double click to their position
    – AutoIt scripts can be excecuted from VBA (the GUI can be started from excel)
    – Mouse clicks can be sent from VBA code (not tested in this case) or included in the script (tested)
    – Pauses are important when working with send keys and mouse (for example, avoid to send clicks before de control is fully loaded)

  9. robertocm

    May 5, 2017

    Post a Reply

    Update2:

    Mouse clicks can be sent directly from VBA to the remote desktop (without the need of using a the embeded Microsoft RDP Client Control mentioned in my previous post) (tested).

    Sorry i don’t remember de source of the next VBA code module:

    Public Declare Function SetCursorPos Lib “user32″ (ByVal x As Long, ByVal y As Long) As Long
    Public Declare Function GetCursorPos Lib “user32″ (lpPoint As POINTAPI) As Long
    Public Declare Sub mouse_event Lib “user32″ (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
    Public Const MOUSEEVENTF_LEFTDOWN = &H2
    Public Const MOUSEEVENTF_LEFTUP = &H4
    Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
    Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
    Public Const MOUSEEVENTF_MIDDLEDOWN = &H20 ‘ middle button down
    Public Const MOUSEEVENTF_MIDDLEUP = &H40 ‘ middle button up

    ‘GetCursorPos requires a variable declared as a custom data type
    ‘that will hold two integers, one for x value and one for y value
    Type POINTAPI
    X_Pos As Long
    Y_Pos As Long
    End Type

    Sub Get_Cursor_Pos()
    ‘Dimension the variable that will hold the x and y cursor positions
    Dim Hold As POINTAPI
    ‘Place the cursor positions in variable Hold
    GetCursorPos Hold
    ‘ Display the cursor position coordinates
    MsgBox “X Position is : ” & Hold.X_Pos & Chr(10) & _
    “Y Position is : ” & Hold.Y_Pos
    End Sub

    Sub Move_the_cursor()
    SetCursorPos 100, 700 ‘Where X and Y are in pixel

    End Sub

    Sub SingleClick()
    SetCursorPos 200, 200 ‘x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub

    Sub DoubleClick()
    ‘Double click as a quick series of two clicks
    SetCursorPos 200, 200 ‘x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub

    Sub RightClick()
    ‘Right click
    SetCursorPos 200, 200 ‘x and y position
    mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
    End Sub

    Sub DragAndDrop()
    SetCursorPos 0, 0 ‘x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    SetCursorPos 200, 200 ‘x and y position
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub

    Sub MiddleClick()
    SetCursorPos 200, 200 ‘x and y position
    mouse_event MOUSEEVENTF_MIDDLEDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_MIDDLEUP, 0, 0, 0, 0
    End Sub

  10. robertocm

    May 18, 2017

    Post a Reply

    Update 3 (final):
    Send keystrokes directly from VBA to remote desktop is not an easy task (discussed for example here: https://www.experts-exchange.com/questions/26943381/VBA-SendKeys-and-Remote-Desktop-Connection.html#a35362208)

    But in my tests it proves very easy to send keystrokes from an AutoIt script with the send function.

    The idea of the example below is:
    – from VBA we can execute an AutoIt script sending the desired keys as parameters
    – from VBA we can send mouse movements to the remote desktop directly

    This is the AutoIt script ‘Send_from_VBA.au3′:
    Send($CmdLine[1])

    (that’s all, only one line of code in the AutoIt file)

    And below the VBA example for sending keystrokes (tested):
    RDPWindow = Shell(“C:\windows\system32\mstsc.exe /v:” & “_________”, 1)

    Application.Wait (Now + TimeValue(“0:00:10″))
    Dim FileAu As String

    FileAu = “C:\MyFolder\Send_from_VBA.au3″
    Shell “C:\Program Files (x86)\AutoIt3\AutoIt3.exe ” & String(1, 34) & FileAu & String(1, 34) & ” ” & String(1, 34) & “#r” & String(1, 34), vbHide

    Application.Wait (Now + TimeValue(“0:00:1″))

    Shell “C:\Program Files (x86)\AutoIt3\AutoIt3.exe ” & String(1, 34) & FileAu & String(1, 34) & ” ” & String(1, 34) & “12345” & String(1, 34), vbHide

Leave a Reply to Thomas Cancel reply