Call Python from VBA (Winwrap)

Question

I am using a software which has a programming interface based on VBA / Winwrap Basic. I would like to use some machine learning libraries from Python, but I am not sure how to build it efficiently.

The software is a dynamic simulation program. Every few time steps I would like to update (train) an artifical neural network (ANN) with new data using Pyhton libraries. I can see it is possible to call a script (like in How to call python script on excel vba?). As I would have to call the script every few times again I am not sure how to handle the ANN object. What is the best way to implement this?

siggi_pop · Answer 1 · 2019-05-22T06:02:13.230

If I understand you correctly, you would like to call a Python script and pass an argument and then perhaps handle the return data object? If you use Windows and have VBA scripting environment, then perhaps you could use WSH (windows script host) object to run the script. First declare the object

Dim wsh As New IWshRuntimeLibrary.WshShell  'early binding

Dim wsh As Object
Set wsh = CreateObject("Wscript.Shell") ' late binding

And use the Run() method on the object, passing object and argument as string. in this example i call powershell and pass scriptfile (run.ps1) as argument. I just call the method, but don't get the return object from the script.

res = wsh.Run("powershell path\run.ps1", WindowStyle:=False, WaitOnReturn:=True)
If res = 0 Then
    Call MsgBox("script ran successfully")
Else
    Call MsgBox("script ran unsuccessfully")
End If

In this example on the other hand, I use the Exec method to fetch the return data as json

res = wsh.Exec("powershell path\run.ps1").StdOut.ReadAll()

Debug.Print res ' return data:

returnObject: 
{
    title: 'theTitle', 
    value: 'the value',
    status: 'status'
}

The run.ps1 basically returns a string:

return "returnObject: 
{
    title: 'theTitle', 
    value: 'the value',
    status: 'status'
}"

Edit: to call a python script file use python or py in place for powershell

res = wsh.Exec("python path\pythonfile.py").StdOut.ReadAll()

Using the 'Run'-method works fine. But when I try the 'Exec'-method it returns an error "not a valid Win32 application". How do you declare 'res' in the second example? I tried 'Dim res As String' and 'Dim res as Object'. — StefanOverFlow, May 20 '19 at 15:18
@StefanOverFlow my example was using a ps1 (powershell) script file, not python. If you wanted to run a python script, then just change the string command and argument like for instance: `res = wsh.Exec("py path\run.py").StdOut.ReadAll()` — siggi_pop, May 22 '19 at 05:48

Call Python from VBA (Winwrap)

1 Answers1