如何在VBA中使用Shell.Run而不是Shell.Exec捕获外壳程序输出? [英] How does one Capturing shell output with Shell.Run instead of Shell.Exec in VBA?

问题描述

我尝试执行以下操作:

我想使用此 answer (解决方案1)在excel-VBA和Python之间传递数据数组.原因是我找不到VBA的scipy方法，因此我这样做.为了捕获来自python的错误消息，我尝试实现类似此的内容.. >

我的VBA代码如下:

 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
 

我的python脚本看起来像这样:

 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)
 

这个想法很简单:

• 使用shell.run或shell.exec(oCmd)调用python脚本

• python脚本从VBA获取它的输入

• python做的事

• python将数据传递回VBA

• 打印发生的错误消息

此VBA代码的问题在于，Python.exe被打开了，但是程序没有被执行.看起来像这样:

打开外壳并闪烁光标. "Hello，User"不会被打印，因此程序不会被执行，因为这通常会在程序中发生任何错误之前运行.如果我关闭此窗口，您好，用户！"在VBA中打印出来，但没有其他内容.

特定问题:如何通过此VBA-Python接口输入和输出一个数组并获取错误消息以在VBA-shell中打印它们?

解决方案

1. 一个选项是使用pandas处理材料"并另存为csv，然后将其拉入VBA作为数组，甚至在python中做所有事情，然后再将最终结果拉入VBA进行格式化，通过电子邮件发送，无论您需要VBA为何.

2. 有关该错误，几乎有相同的问题，并能够解决@@ strong> MichaelButscher . 链接.基本上，您需要像

   一样使用错误重定向

   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

跟踪致命错误(err.txt)或预期输出(out.txt).

我希望这对您有帮助

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?

解决方案

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

这篇关于如何在VBA中使用Shell.Run而不是Shell.Exec捕获外壳程序输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！