Calling Python from VBA - returning Python function value

Question

I call Python script from VBA. The structure of this Python script is a single function without anything else:

def myFunction():
    # do stuff
    return stuff_output

I know that this function works properly, because if added, print(myFunction()) generates correct output (JSON response) in command line.

In my VBA code 'RetVal` is always a 4 digit integer value. Perhaps Python function isn't even executed?

Which code and how should I fix to make it work? I would appreciate a solution which is close to my current code and without external libraries if possible.

Option Explicit

Sub RunPython()

    Dim RetVal
    Dim scriptPath As String

    scriptPath = "C:\Users\Rychu\Desktop\python\myPythonScript.py"
    RetVal = Shell("C:\Python36-32\python.exe" & " " & scriptPath, vbHide)

    If RetVal = 0 Then
        MsgBox "Couldn't run python script!", vbOKOnly
    Else
        Debug.Print RetVal
    End If

End Sub

Answer 1

I couldn't find any direct way to transfer value returned by Python function, so I handled it by saving Python result to text file on Desktop and reading + deleting the same file with VBA.

Important things are:

1. shellObj.Run unlike Shell allows to set a Boolean parameter to wait for return to True, so that the rest of VBA code doesn't try to get data from text file while there isn't any created yet.

2. If text file doesn't have a full file path specified, only a file name, Python won't create text file in directory in which Python script is located, as it happens when I run script through command line or Visual Studio. Apparently when called from VBA, Python has a different perception of default directory and it isn't a directory of VBA file either.

Here is my Python code:

def myFunction():
    # do stuff
    return stuff_output

import os

f = open(os.path.expanduser("~\Desktop") + "\myTemp.txt","w")   #create a new text file on Desktop in write mode
f.write(str(myFunction()))  #write output to created text file 
f.close()   #close file object

Here is my VBA code:

Option Explicit

Sub RunPython()

    Dim shellObj As Object: Set shellObj = CreateObject("WScript.Shell")
    Dim tempTxtPath As String

    Const scriptPath As String = "C:\Users\Rychu\Desktop\python\myPythonScript.py"
    Const pythonPath As String = "C:\Python36-32\python.exe"
    tempTxtPath = CreateObject("WScript.Shell").specialfolders("Desktop") & "\myTemp.txt"

    shellObj.Run pythonPath & " " & scriptPath, vbHide, True

    Debug.Print (getTextAndDelete(tempTxtPath))

End Sub

Function getTextAndDelete(filePath) As String

    Dim fileNum As Long
    Dim myData As String

    fileNum = FreeFile
    Open filePath For Binary As #fileNum

    myData = Space$(LOF(1))
    Get #fileNum, , myData
    Close #fileNum

    Kill filePath

    getTextAndDelete = myData

End Function