CODEWITHSUNDEEP
pythonexcelvbapowershellshell
Determinant
Determinant

Reputation: 28

VBA Shell on remote connection

This code works if I run it on my local machine on the C drive. But, if I try to run it from a spreadsheet on a remote machine in a session where I'm logged into that same remote machine, nothing happens. It doesn't crash per se, it just doesn't produce anything.

...
Call RunCmd("python37 get_eff_req_data.py HPBoiler_RunMgr_v00.txt")
...

Sub RunCmd(cmd)
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    
    ChDir ThisWorkbook.Path
    Call wsh.Run(cmd, windowStyle)
End Sub

If I run that exact command line in a PowerShell window on the remote machine in the directory of the spreadsheet, it works fine. Maybe it's a factor that the spreadsheet that has this macro is on the D drive, while Python is on the C drive on the remote machine? But then why does it run fine in Powershell? I will say that when the VBA macro tries to run the shell it opens up the regular DOS Command Prompt, not Powershell, and when I open a Command Prompt on my own it goes to another drive, the H drive. I can't get to the D drive from the Command Prompt but I have confirmed that Python will run on H.

Upvotes: 0

Views: 335

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

FYI

ChDir ThisWorkbook.Path

will only work if the Current Directory is on the same drive as the path you're trying to set. If it's on a different drive then you first need to call ChDrive to set the correct drive.

Command prompt won't change directory to another drive

Upvotes: 1

Related Questions