0

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.

Determinant
  • 28
  • 4
  • You are assuming that `get_eff_req_data.py` and `HPBoiler_RunMgr_v00.txt` are in the current directory, whatever that is. How do you know that? You might want to use the `Dir()` function to make sure those files actually exist. And are you sure this other computer has Python installed, and its directory is in the path? – Tim Roberts Mar 15 '21 at 00:54
  • 1
    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... https://stackoverflow.com/questions/11065421/command-prompt-wont-change-directory-to-another-drive – Tim Williams Mar 15 '21 at 01:03
  • @TimRoberts thanks. yes, I’m sure they’re in the current directory. I put them there and see them there. I’m also sure that Python works because if I run the same command in Powershell in that directory, the command runs as I expect it to. – Determinant Mar 16 '21 at 14:59
  • @TimWilliams This was it. Changed the drive first now it works as expected. If you want to post this as an answer I’d checkmark it. – Determinant Mar 16 '21 at 22:18

1 Answers1

1

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

Tim Williams
  • 154,628
  • 8
  • 97
  • 125