使用Python编写VBA脚本？ [英] Use Python to Write VBA Script?

问题描述

这可能是一个延伸，但是有可能使用python脚本来使用pythonwin或任何其他模块在MS Excel（或使用VBA的任何其他MS Office产品）中创建VBA。 p>

这个想法来自于pythons openpyxl模块无法执行列自动宽度。脚本我在内存中创建了一个工作簿，最终将其保存到光盘。有几张床单，每张床单中有不少列。我想到....如果我只是使用python将VBA脚本（保存在记事本或某些东西中）导入到Excel中的VBA编辑器中，然后使用pythonwin从python运行该脚本。

如下所示：

  Workbooks.worksheets.Columns（A：Z）EntireColumn。 Autofit 
  

在您评论之前，是的，我看到很多关于如何解决自动调整列的pythonic示例在openpyxl中，但是我看到一些有趣的机会，可以利用从VBA获得的功能，可能在python中可用。

无论如何，我在互联网上挖有一点，我没有看到任何表示我可以的，所以我以为我会问。

干杯，
麦克

解决方案

是的，这是可能的。您可以开始查看如何从VB在该Microsoft知识库生成VBA宏。

下面的Python代码说明了如何做到这一点;它是KB示例代码上半部分的基本端口：

 导入win32com.client作为win32 
 
 import comtypes，comtypes.client 
 
 xl = win32.gencache.EnsureDispatch（'Excel.Application'）
 xl.Visible = True 
 ss = xl.Workbooks。 Add（）
 sh = ss.ActiveSheet 
 
 xlmodule = ss.VBProject.VBComponents.Add（1）＃vbext_ct_StdModule 
 
 sCode ='''sub VBAMacro 
 msgboxVBA宏调用
 end sub'''
 
 xlmodule.CodeModule.AddFromString（sCode）
  

您可以查看可见的自动Excel宏，您将看到上面定义的 VBAMacro

This might be a bit of a stretch, but is there a possibility that a python script can be used to create VBA in MS Excel (or any other MS Office product that uses VBA) using pythonwin or any other module.

Where this idea came from was pythons openpyxl modules inability to do column autowidth. The script I have creates a workbook in memory and eventually saves it to disc. There are quite a few sheets and within each sheet, there are quite a few columns. I got to thinking....what if I just use python to import a VBA script (saved somewhere in notepad or something) into the VBA editor in excel and then run that script from python using pythonwin.

Something like:

Workbooks.worksheets.Columns("A:Z").EntireColumn.Autofit

Before you comment, yes I have seen lots of pythonic examples of how to work around auto adjusting columns in openpyxl, but I see some interesting opportunities that can be had utilizing the functionality you get from VBA that may not be available in python.

Anyways, I dug around the internet a bit and I didn't see anything that indicates i can, so i thought I'd ask.

Cheers, Mike

解决方案

Yes, it is possible. You can start looking at how you can generate a VBA macro from VB on that Microsoft KB.

The Python code below is illustrating how you can do the same ; it is a basic port of the first half of the KB sample code:

import win32com.client as win32

import comtypes, comtypes.client

xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
ss = xl.Workbooks.Add()
sh = ss.ActiveSheet

xlmodule = ss.VBProject.VBComponents.Add(1)  # vbext_ct_StdModule

sCode = '''sub VBAMacro()
       msgbox "VBA Macro called"
      end sub'''

xlmodule.CodeModule.AddFromString(sCode)

You can look at the visible automated Excel macros, and you will see the VBAMacro defined above.

这篇关于使用Python编写VBA脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！