How does one Capturing shell output with Shell.Run instead of Shell.Exec in VBA?

Question

I try do do the following:

I want to use the code described in this answer (Solution 1) to pass arrays of data between excel-VBA and Python. Reason for this is that I cannot find a scipy method for VBA and therefore I do it like this. To catch error messages from python I tried to implement something like this.

My VBA code looks like this:

Sub Usage2()
Dim outputarr() As Double
Dim oShell As Object, oCmd As String
Dim oExec As Object, oOutput As Object
Dim arg As Variant
Dim s As String, sLine As String

Set oShell = CreateObject("WScript.Shell")
    oCmd = "<fullpathtopython> " & """" & "<fullpathtoscript>" & """"
' Make the data available via GetData()'
Cache = Array(4, 6, 8, 9)

Set oExec = oShell.Exec(oCmd)
Set oOutput = oExec.StdOut

While Not oOutput.AtEndOfStream
    sLine = oOutput.ReadLine
    If sLine <> "" Then s = s & sLine & vbNewLine
Wend

Debug.Print s
' Handle the returned data '
Debug.Assert Cache(3) = 8

Set oOutput = Nothing: Set oExec = Nothing
Set oShell = Nothing
End Sub

my python script looks like this:

import win32com.client
import numpy as np
import os
import matplotlib.pyplot as plt
import win32com.client
from scipy.interpolate import LSQUnivariateSpline, UnivariateSpline

print "Hello, User!"

# get the running instance of Excel
app = win32com.client.GetObject(Class="Excel.Application")

# get some data from Excel
data = app.run("GetData") # this OR

ssc=np.array(data) # this probably makes an error
print ssc

#do some fitting ...

# return some data to excel
app.run("SetData", sscnew)

The idea is simple:

• call python script using shell.run or shell.exec(oCmd)

• python script gets it's input from VBA

• python does stuff

• python passes data back to VBA

• print error messages that occured

The problem with this VBA code is, that Python.exe gets opened but the program does not get executed. It looks like this:

Open shell and blinking cursor. The 'Hello, User' doesn't get printed so the program doesn't get executed since this usually runs through before any errors occure in the program. If I close this window, 'Hello, User!' gets printed in VBA but nothing else.

Specific question: How does one input and output arrays via this VBA-Python interface AND get the errormessages to print them in the VBA-shell?

Answer 1

1. One option is processing "stuff' with pandas and save as csv, then pull into VBA as array(s) or even do everything in python before pulling the end result in VBA for formatting, emailing, whatever you need VBA for.

2. regarding the error, had almost the same issue and was able to resolve thank to @MichaelButscher . link. Basically you need to use error redirecting as in

   import sys path_err = r'\\yourpath\test\err.txt' path_out = r'\\yourpath\test\out.txt' sys.stderr = open(path_err, 'w') sys.stdout = open(path_out, 'w') path = r'\\yourpath\test\test1.txt' with open (path, "w") as f: f.write("test1") raise IndexError

to track fatal errors (err.txt) or expected output (out.txt).

I hope this helps