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

问题描述

我有一个python代码，读取3个参数（标量）和一个文本文件，然后给我一个双向的向量。我想在vba中写一个宏来调用这个python代码，并将结果写在同一个excel表中。我想知道最简单的方法是什么，这里有一些我发现的东西：

• 调用shell ）函数在vba中，但是看起来并不容易得到返回值。




• 将python代码注册为COM对象，并从vba- - >我不知道如何做，所以如果你有一些例子，这将是不欢迎的




• 在自定义工具箱中创建一个自定义工具，在vba中创建一个地理处理对象，然后再添加一个addtoolbox，然后我们可以直接通过地理处理对象使用自定义工具，但是这样的东西，我不知道该怎么做..

任何提示？

解决方案

p>

1. 转到Activestate并获取 ActivePython 2.5.7 MSI安装程序。
   
   我有2.6.x的DLL地狱问题


2. 安装在您的Windows机器中



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

   
   

   C：\Python25\lib\site-packages\win32comext\axscript\client

   
   




4. 执行 \> python pyscript.py
   您应该看到消息注册：Python




5. 转到ms office excel和打开工作表



6. 转到工具>宏> Visual Basic编辑器


7. 添加对 Microsoft Script控件的引用


8. 添加新的用户表单。在UserForm中添加一个CommandButton



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

   
   

   Dim WithEvents PyScript As
   MSScriptControl.ScriptControl

     Private Sub CommandButton1_Click（）
   如果PyScript不是然后
   设置PyScript =新的MSScriptControl.ScriptControl 
    PyScript.Language =python
    PyScript.AddObjectSheet，工作簿（1）.Sets（1）
    PyScript.AllowUI = True 
    End If 
    PyScript.ExecuteStatementSheet.cells（1,1）.value ='Hello'
    End Sub 
     

   
   

执行。必要时享受和展开

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:

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

• 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

• 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..

Any tips?

解决方案

Follow these steps carefully

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:\Python25\lib\site-packages\win32comext\axscript\client

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

5. Go to ms office excel and open worksheet

6. Go to Tools > Macros > Visual Basic Editor
7. Add a reference to the Microsoft Script control
8. Add a new User Form. In the UserForm add a CommandButton

9. Switch to the code editor and Insert the following code

   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屋！