编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

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

codejedi365 Python 2022-5-11 10:53 5人围观

腾讯云服务器
如何在OSX上从Excel 365 VBA启动外部python进程?的处理方法

我在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进程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^