1

I am trying to automate a process which involves using VBA in Excel to open a file on a remote desktop through RDP. I have successfully managed to log into RDP but am now struggling to open the file consistently. I wrote some code relying on SendKeys that maybe works 10% of the time but am looking for something more robust. 

Sub RunRDP()
Dim RetVal As Variant
Dim Target As String
Dim Sheet As Variant

'Log-in info
Target = "AAAA.com"
UserName = "BBBBBB\CCC"
Pwd = "DDDDD"

'Connect to Remote Desktop
RetVal = Shell("cmdkey /generic:""" & Target & """ /user:""" & UserName & """ /pass:""" & Pwd & """", 3)
RetVal = Shell("c:\Windows\System32\mstsc.exe /v:" & Target, 3)

'Press yes through cert errors
Do
    If InStr(ActiveWinTitle, "Remote Desktop Connection") > 0 Then
        Application.SendKeys "y", True
    End If
Loop Until InStr(ActiveWinTitle, "AAAA") > 0
Application.Wait (Now + TimeValue("00:00:03"))
If InStr(ActiveWinTitle, "Remote Desktop Connection") > 0 Then
    AppActivate "AAAAA.com - Remote Desktop Connection"
Else
    AppActivate "AAAAA.com"
End If
Application.Wait (Now + TimeValue("00:00:07"))

The above code works as expected. ActiveWinTitle is a function to grab the current window's caption, see below:

Public Declare Function GetForegroundWindow Lib "user32" _
() As Long
Public Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" (ByVal HWnd As Long, _
ByVal lpString As String, ByVal cch As Long) As Long

Public Function ActiveWinTitle() As String
    Dim WinText As String
    Dim HWnd As Long
    Dim L As Long
    HWnd = GetForegroundWindow()
    WinText = String(255, vbNullChar)
    L = GetWindowText(HWnd, WinText, 255)
    ActiveWinTitle = Left(WinText, InStr(1, WinText, vbNullChar) - 1)
End Function

The below code is what I've tried to make work for opening the file. Its explanation in English follows:

Application.SendKeys "RE", True
Application.SendKeys "~", True
Application.Wait (Now + TimeValue("00:00:01"))
Application.SendKeys "{F4}", True
Application.Wait (Now + TimeValue("00:00:01"))
Application.SendKeys "{BS}{BS}{BS}{BS}{BS}{BS}{BS}{BS}{BS}{BS}{BS}F:\[**FILEPATH HERE**]~", True
  1. Type RE on the desktop to highlight the Recycle Bin
  2. Press Enter to open the Recycle Bin (to get to a file explorer window)
  3. Wait one second
  4. Press F4 to move cursor to address bar
  5. Wait one second
  6. Delete "Recycle Bin" from the address bar, write in the correct filepath, and press Enter

Obviously this is extremely unreliable and is the reason I'm looking for something better.

This code is something I'm using for work and am looking to share with my colleagues - I am not able to download any programs to use instead of VBA because of this.

I have looked at these questions without much avail:

Script to Open a batch file on a remote computer I am not familiar with WMI and am not sure if I would have to completely replace using RDP. I tried looking at the documentation for it and it's quite above my head.

Run a batch file on a remote desktop from VBA This is an earlier thread from the same user. It has some dead links that I was unable to follow.

I've looked at a lot of threads that had the same unanswered question as mine. It may be a futile effort, but I'd like to know definitively if this is manageable or not. [EDIT: Some of the unanswered forum posts I've found in my research below]

Thanks in advance for all of your help.

Salvaria
  • 51
  • 1
  • 9
  • What exactly are you trying to do here? Run an executable on a remote desktop or ...? – Tim Williams Nov 30 '18 at 17:46
  • It's actually opening another excel file that sits on a shared drive between the two computers. I am doing it this way because I have an intranet site that can only be opened on the remote desktop. The second excel file has more VBA in it that runs on worksheet_open, which is used to open the intranet site, fill in some fields, and downloads an CSV. – Salvaria Nov 30 '18 at 18:19

1 Answers1

2

Would a non-programming approach help you, too?

On the remote computer, create a scheduled task which launches when someone connects to the user session.

enter image description here

...and simply run anything from there.

Of course, maybe you still want to reach only for advanced techniques, but sometimes they can be easily avoided only by using existing tools.

miroxlav
  • 11,796
  • 5
  • 58
  • 99
  • This is interesting and I wasn't aware of something like this - however, would this trigger _every_ time someone opened the RDP? It's used for more things than just what I'm doing, and I wouldn't want to create something that would run whenever anybody accessed it for any reason. – Salvaria Nov 30 '18 at 18:23
  • @Salvaria – Yes, every time. But I posted that because your question could be read like doing the stuff every time on connecting. – miroxlav Nov 30 '18 at 18:32
  • I could see where my question could sound like that. I really appreciate your suggestion because I have not thought of using a non-programming solution. This could definitely help someone else looking to do something similar, but unfortunately will not work in this specific case. Thank you! – Salvaria Nov 30 '18 at 18:37
  • @Salvaria – Tell me about your scenario. Is it rather static (seldom setup) or will it be deployed frequently without any reach of your team? Can the remote computer see your main computer on the network? Because if yes, you can deploy a command file to shared location and then execute it from remote computer. (Of course, there still are better solutions but this one is quite light-weight.) This way you can always run only the commands you want (or run empty batch file). – miroxlav Nov 30 '18 at 21:27
  • My plan is to run this multiple times each month with different users running it from their own laptops. It appears that the remote computer can see the local one (I can see files in my local C drive from the remote computer). – Salvaria Dec 03 '18 at 14:36
  • @Salvaria – then you can use relatively easy solution by writing batch file to local drive by your app and run it with the above scheduled task. This way you can control what is inside it. Of course, there are more advanced solutions but I am attempting to stay simple. – miroxlav Dec 03 '18 at 15:00