如何在OSX上从Excel 365 VBA启动外部python进程? [英] How can I launch an external python process from Excel 365 VBA on OSX?

问题描述

我在OSX的Excel 365中有一个VBA宏，我希望启动一个外部进程(一个python脚本)，该进程将生成一个文件导入到Excel中.

I have a VBA macro in Excel 365 on OSX that I want to have launch an external process (a python script) that will generate a file for importing into Excel.

我尝试了很多事情，但是没有一个对我有用.我在下面列出了它们.

I've tried a number of things, but none of them are working for me. I've listed them below.

我的第一次尝试是使用:

My first attempt was to use:

Shell ("/usr/local/bin/python /tmp/myScript.py")
>> Run-time error '76': Path not found

然后我读到它更喜欢旧的Mac风格的路径，

Then I read that it preferred old Mac-style paths, so did

Shell ("Macintosh HD:usr:local:bin:python /tmp/myScript.py")
>> Run-time error '76': Path not found

在我的系统上，这是一个符号链接，因为我正在使用HomeBrew，因此我确定了链接指向的位置，然后运行该链接:

This, on my system, is a symbolic link, as I'm using HomeBrew, so I resolved where the link was pointing and ran that instead:

Shell ("Macintosh HD:usr:local:Cellar:python:2.7.10_2:Frameworks:Python.framework:Versions:2.7:bin:python /tmp/myScript.py")
>> Run-time error '76': Path not found

以防万一，我尝试使用unix样式的路径:

And just in case, I tried that one with unix-style paths:

Shell ("/usr/local/Cellar/python/2.7.10_2/Frameworks/Python.framework/Versions/2.7/bin/python /tmp/myScript.py")
>> Run-time error '76': Path not found

然后我尝试使用MacScript运行它:

Then I tried running it using MacScript:

MacScript ("do shell script " + Chr(34) + "/usr/local/bin/python /tmp/myScript.py" + Chr(34))
>> Run-time error '5': Invalid procedure call or argument

所以在我看来，MacScript()在此版本的VBA中不可用.

So it seems to me that MacScript() isn't available in this version of VBA.

然后我找到了一篇文章，描述了如何在libc中揭示 system 方法:

Then I found an article describing how to reveal the system method in libc:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long
system("/usr/local/bin/python /tmp/myScript.py")
>> Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update DEclare statements and then mark them with the PtrSafe attribute.

多做一些挖掘工作以检查应该在哪里添加，这给了我

Doing a bit more digging to check where I should add this gave me:

Private Declare PtrSafe Function system Lib "libc.dylib" (ByVal command As String) As Long
system("/usr/local/bin/python /tmp/myScript.py")
>> Run-time error '453': File not found: libc.dylib

考虑到它可能再次与符号链接有关，我发现有一个符号链接链. libc.dylib->libSystem.dylib->libSystem.B.dylib ，所以我最后尝试的是:

Thinking that it might be sym-link related again, I found that there was a chain of sym links libc.dylib -> libSystem.dylib -> libSystem.B.dylib, so the last thing I tried was:

Private Declare PtrSafe Function system Lib "libSystem.B.dylib" (ByVal command As String) As Long
system("/usr/local/bin/python /tmp/myScript.py")
>> Run-time error '453': File not found: libSystem.B.dylib

就我所能做到的.有人可以从这里向我指出正确的方向吗?

And that was as far as I managed to get. Can someone point me in the right direction from here?

谢谢

推荐答案

花了我一段时间，但我想出了解决方法.

It took me a while, but I figured out how to do it.

Excel 2016已将 MacScript()替换为 AppleScriptTask().需要为此提供一个AppleScript文件和要调用的函数，而据我所知，它无法获得原始的AppleScript命令.

Excel 2016 has replaced MacScript() with AppleScriptTask(). This needs to be given an AppleScript file and function to call, and can't, as far as I am aware, be given raw AppleScript commands.

我创建了一个名为PythonCommand.scpt的AppleScript文件，其中包含以下内容:

I created an AppleScript file called PythonCommand.scpt which contained something like:

on PythonCommand(pythonScript)
    do shell script "/usr/local/bin/python " & pythonScript
end PythonCommand

然后需要将该AppleScript文件放置在〜/Library/Application Scripts/com.microsoft.Excel/中(如果此文件夹不存在，请创建该文件夹)

This AppleScript file needs to be then placed in ~/Library/Application Scripts/com.microsoft.Excel/ (create this folder if it doesn't exist)

一旦在这里，就可以使用以下函数调用该函数:

Once this is here, the function can be called using:

Dim result As String
result = AppleScriptTask("PythonCommand.scpt", "PythonCommand", pythonScript)

(它坚持要返回东西-没有它就无法工作)

(it insists on returning something - it won't work without this)

安全条款:

• 任何Excel宏都可以调用此AppleScript.宏仍然可以做令人讨厌的事情，但是还是值得在其中进行一些保护，以避免执行其他代码.

这篇关于如何在OSX上从Excel 365 VBA启动外部python进程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！