Controling Excel Instance on a Remote Virtual Machine with VBA-CodePudding

I work in a very big international group. In order to get data that is common to the group, I need to connect to a remote virtual machine (VM) using Citrix Receiver.

This VM is programmed in a way that when someone, that is not an admin, is connected to it, it launches a temporary Excel. Note that, the person that is not an admin can only have access to this Excel instance on the VM.

In this Excel instance there is an AddIn that I use to query data provided by the group that I need to get every day. I do this manually and I want to automate it.

I already managed to write a code that is run localy. This code connects to the VM through a company website and so I have the temporary Excel open in front of me. Here is the code and how the temporary Excel is open in front of me:

Dim ie As Object
Dim doc As New HTMLDocument
Dim usn_box As HTMLInputElement
Dim pwd_box As HTMLInputElement
Dim btn As HTMLButtonElement
Dim img As HTMLImg

Set ie = New InternetExplorerMedium ' needs this and not usual internetExplorer
ie.Visible = True
ie.Silent = True
ie.Navigate "https://the.path"

While ie.Busy Or ie.ReadyState <> 4
    DoEvents
Wend

Application.Wait Now   TimeSerial(0, 0, 5)
Set doc = ie.Document

'if error on usn_box go directly to image and click it cause you are logged in
Set usn_box = doc.querySelector("#username")
If Not usn_box Is Nothing Then
    usn_box.Value = usn

    Set pwd_box = doc.querySelector("#password")
    pwd_box.Value = pwd

    Set btn = doc.querySelector("#loginBtn")
    btn.Click

    Application.Wait Now   TimeSerial(0, 0, 5)
End If

Set img = doc.querySelector("#home-screen > div.home-view-content > section.scrollable.storeViewSection > div.store-apps-container > ul > li:nth-child(1) > a.storeapp-details-link > img")
img.Click
Application.Wait Now   TimeSerial(0, 0, 20)

Now what I need is to be able to control this Excel using my VBA code to be able to use this Data Genic AddIn. How can I do this ?

I tried checking how to control excel instance from another, but since the instance in question is on a remote VM, my local Excel cannot see it. Also, there is no other way to connect to the VM that is not going through the website.

Help me oh wise and great SO gods.

EDIT In response to Keith Swerling's comment. I clicked on file and the browse. I saw that there is a folder named CTRXPRD that has subfolders. Each of these subfolders has the Entreprise ID of a person that has access to the VM.

When I clicked on Browse I started in my folder and more specifically in the documents subfolder. So it seems like each entreprise ID has a windows account on this VM.

How can this help me ?

CodePudding user response：

I found a massive work around. Since I can save files on the VM and since I have access to my local server from the VM, but not the other way around:

Create a macro enabled workbook (MEWB)
This MEWB has all the macros that you need to get the data and to save them in one or many Excel files on the local server
Once you are happy with these macros, create a macro in this MEWB that runs when this MEWB is open. This macro that runs on open just calls all the others
Save this MEWB as an addin (.xlam)
Enable this ADDIn on Excel, just like you would the Solver AddIn or any other add in

Now everytime an Excel instance is open all the macros you wrote will run and so everytime you connect to the VM, they will all run and you will have the data stored locally.

It's a hell of a work around, but it works.