2

Scenario: I am trying to run a python script from a vba code in excel. The code runs with no errors, but yields no results whatsoever.

Code in VBA:

Private Sub CommandButton1_Click()

    Dim Ret_Val
    Dim args As String

    args = "\\X\s\D\D\Data_manager.py"
    Ret_Val = Shell("C:\Users\D\AppData\Local\Continuum\anaconda2\python.exe" & " " & args, vbNormalFocus)

'also tried this, same result
Ret_Val = Shell("C:\Users\D\AppData\Local\Continuum\anaconda2\python.exe \\X\s\D\D\Data_manager.py")

End Sub

Question: I ran this same code a couple of months ago and it worked just fine, but now, for some reason it is behaving weirdly. Any ideas on what might be happening?

OBS1: I also read other posts in SO with similar problems, but for those, VBA seems to be throwing an error, which is something that does not happen in my code.

OBS2: I know the code is not yielding any result, because my python script is pretty simple, it asks for a couple of inputs and outputs them to a csv file.

DGMS89
  • 1,507
  • 6
  • 29
  • 60
  • How do you know the code is not returning a result? – David Rushton Jan 02 '18 at 13:36
  • Did you check all the obvious things to check? Like running the same script from windows command line to see if it really works, – Siyon DP Jan 02 '18 at 13:52
  • @destination-data Because the other than output to a csv file, my python script also prompts a raw input in the end, to state when it is completed. – DGMS89 Jan 02 '18 at 14:48
  • @TSion.D.P I checked everything I could remember, but am probably missing something. If I run the python script directly from python (spyder), it runs smoothly. The problem is that, albeit it is opening the script, it is not running it for some reason (when I use the VBA connection). – DGMS89 Jan 02 '18 at 14:50
  • What's the value of `Ret_Val` after the call to shell? Does Python.exe appear in the task manager when you run this code? – David Rushton Jan 02 '18 at 14:56
  • @destination-data Ret_Val is being returned 3862 as I run line by line, and yes it opens python, but closes it without doing anything. – DGMS89 Jan 02 '18 at 15:12
  • @DGMS89 Try running from Win cmd line, Win button+r then paste the first arg of the Shell funct – Siyon DP Jan 03 '18 at 12:51

1 Answers1

1

Should be just like this.

RetVal = Shell("<full path to python.exe> " & "<full path to your python script>")

Or if the python script is in the same folder as the workbook, then you can try :

RetVal = Shell("<full path to python.exe> " & ActiveWorkBook.Path & "\<python script name>")

How to call python script on excel vba?

Or, try this:

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run "cmd.exe /S /C perl a.pl c:\temp", windowStyle, waitOnReturn
ASH
  • 20,759
  • 19
  • 87
  • 200
  • From the link you posted, the author of the OP said his problem was solved in the comments to that answer. If you check his comment, you will see that it is exactly the same the part that is commented out of my code. I also tried what was stated in the answer itself. Same result. – DGMS89 Jan 03 '18 at 13:34