从 excel/vba 调用 python 脚本 [英] Calling python script from excel/vba

问题描述

我有一个 python 代码，它读取 3 个参数(标量)和一个文本文件，然后返回一个双精度向量.我想在 vba 中编写一个宏来调用此 python 代码并将结果写入同一张 Excel 表中.我想知道最简单的方法是什么，这里有一些我发现的东西:

I have a python code that reads 3 arguments (scalars) and a text files and then returns me a vector of double. I want to write a macro in vba to call this python code and write the results in one of the same excel sheet. I wanted to know what was the easiest way to do it, here are some stuffs that I found:

• 在vba中调用shell()函数，但是获取返回值似乎不太容易.

• call the shell() function in vba but it doesn't seem so easy to get the return value.

将 python 代码注册为 COM 对象并从 vba 调用它--> 我不知道该怎么做，所以如果你有一些例子，那就太受欢迎了

register the python code as a COM object and call it from vba--> i don't know how to do that so if you have some examples it would be more than welcome

在自定义工具箱中创建自定义工具，在 vba 中创建一个地理处理对象，然后添加工具箱，然后我们可以通过地理处理对象直接使用自定义工具，但这也是我不知道如何要做..

create a custom tool in a custom toolbox, in vba create a geoprocessing object and then addtoolbox and then we can use the custom tool directly via the geoprocessing object but this is something as well that I don't know how to do..

有什么建议吗?

推荐答案

仔细按照这些步骤操作

1. 转到 Activestate 并获取 ActivePython 2.5.7 MSI 安装程序.
   我在 2.6.x 中遇到了 DLL 地狱问题
2. 在您的 Windows 机器上安装

3. 安装完成后打开命令提示符并转到

1. Go to Activestate and get ActivePython 2.5.7 MSI installer.
   I had DLL hell problems with 2.6.x
2. Install in your Windows machine

3. once install is complete open Command Prompt and go to

C:Python25libsite-packageswin32comextaxscriptclient

C:Python25libsite-packageswin32comextaxscriptclient

执行<代码>>python pyscript.py您应该看到消息 Registered: Python

execute > python pyscript.py you should see message Registered: Python

转到 ms office excel 并打开工作表

Go to ms office excel and open worksheet

切换到代码编辑器并插入以下代码

Switch to the code editor and Insert the following code

Dim WithEvents PyScript AsMSScriptControl.ScriptControl

Dim WithEvents PyScript As MSScriptControl.ScriptControl

Private Sub CommandButton1_Click()
   If PyScript Is Nothing Then
       Set PyScript = New MSScriptControl.ScriptControl
       PyScript.Language = "python"
       PyScript.AddObject "Sheet", Workbooks(1).Sheets(1)
       PyScript.AllowUI = True
   End If
   PyScript.ExecuteStatement "Sheet.cells(1,1).value='Hello'"
End Sub

执行.根据需要享受和扩展

Execute. Enjoy and expand as necessary

这篇关于从 excel/vba 调用 python 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！