使用Python将宏注入电子表格 [英] Use Python to Inject Macros into Spreadsheets

问题描述

我有一个宏，我想要一堆现有的电子表格使用。唯一的问题是有这么多的电子表格，手工耗费太多时间！

我已经编写了一个Python脚本来使用pyWin32访问所需的文件，但是我似乎无法找出使用它来添加宏的方法。

这里提供了一个类似的问题（这不是Python，但它看起来仍然使用COM），但我的COM对象似乎没有一个名为VBProject：

 设置objExcel = CreateObject（Excel.Application）
 objExcel.Visible = True 
 objExcel.DisplayAlerts = False 
设置objWorkbook = objExcel.Workbooks.Open（C：\scripts\test.xls）
设置xlmodule = objworkbook.VBProject.VBComponents.Add（1）
 strCode = _ 
sub test（）& vbCr& _ 
msgbox宏内& vbCr& _ 
end sub
 xlmodule.CodeModule.AddFromString strCode 
 objWorkbook.SaveAsc：\scripts\test.xls
 objExcel.Quit 
  

编辑：链接到引用的类似问题：将Excel VBA代码注入并执行从外部来源接收的电子表格

我也忘了提到，虽然这不是Python，但我希望通过COM对象可以使用类似的对象成员。

解决方案

这是代码转换。您可以使用 win32com 或

 导入os 
 import sys 
 
＃导入系统库
 import glob 
 import random 
 import re 
 
 sys.coinit_flags = 0＃ comtypes.COINIT_MULTITHREADED 
 
＃使用COMTYPES或WIN32COM 
 #import comtypes 
 #from comtypes.client import CreateObject 
 
＃USE COMTYPES或WIN32COM 
 import win32com 
 from win32com.client import Dispatch 
 
 scripts_dir =C：\\\scripts
 conv_scripts_dir =C：\\\convertted_scripts
 strcode = \ 
'''
 sub test（）
 msgbox里面的宏
 end sub 
'''
 
 #com_instance = CreateObject（Excel.Application，dynamic = True）＃使用COMTYPES 
 com_instance = Dispatch（Excel.Appl ication）＃USING WIN32COM 
 com_instance.Visible = True 
 com_instance.DisplayAlerts = False 
 
为glob.glob中的script_file（os.path.join（scripts_dir，*。 xls））
打印处理：％s％script_file 
（file_path，file_name）= os.path.split（script_file）
 objworkbook = com_instance.Workbooks.Open（script_file） 
 xlmodule = objworkbook.VBProject.VBComponents.Add（1）
 xlmodule.CodeModule.AddFromString（strcode.strip（））
 objworkbook.SaveAs（os.path.join（conv_scripts_dir，file_name） ）
 
 com_instance.Quit（）
  

I've got a macro that I'd like a bunch of existing spreadsheets to use. The only problem is that there are so many spreadsheets that it would be too time consuming to do it by hand!

I've written a Python script to access the needed files using pyWin32, but I can't seem to figure out a way to use it to add the macro in.

A similar question here gave this answer (it's not Python, but it looks like it still uses COM), but my COM object doesn't seem to have a member called VBProject:

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
objExcel.DisplayAlerts = False 
Set  objWorkbook = objExcel.Workbooks.Open("C:\scripts\test.xls") 
   Set xlmodule = objworkbook.VBProject.VBComponents.Add(1)  
   strCode = _ 
   "sub test()" & vbCr & _ 
   "   msgbox ""Inside the macro"" " & vbCr & _ 
   "end sub" 
   xlmodule.CodeModule.AddFromString strCode 
objWorkbook.SaveAs "c:\scripts\test.xls" 
objExcel.Quit 

EDIT: Link to the similar question referenced: Inject and execute Excel VBA code into spreadsheet received from external source

I also forgot to mention that although this isn't Python, I was hoping that similar object members would be available to me via the COM objects.

解决方案

This is the code converted. You can use either the win32com or comtypes packages.

import os
import sys

# Import System libraries
import glob
import random
import re

sys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED

# USE COMTYPES OR WIN32COM
#import comtypes
#from comtypes.client import CreateObject

# USE COMTYPES OR WIN32COM
import win32com
from win32com.client import Dispatch

scripts_dir = "C:\\scripts"
conv_scripts_dir = "C:\\converted_scripts"
strcode = \
'''
sub test()
   msgbox "Inside the macro"
end sub
'''

#com_instance = CreateObject("Excel.Application", dynamic = True) # USING COMTYPES
com_instance = Dispatch("Excel.Application") # USING WIN32COM
com_instance.Visible = True 
com_instance.DisplayAlerts = False 

for script_file in glob.glob(os.path.join(scripts_dir, "*.xls")):
    print "Processing: %s" % script_file
    (file_path, file_name) = os.path.split(script_file)
    objworkbook = com_instance.Workbooks.Open(script_file)
    xlmodule = objworkbook.VBProject.VBComponents.Add(1)
    xlmodule.CodeModule.AddFromString(strcode.strip())
    objworkbook.SaveAs(os.path.join(conv_scripts_dir, file_name))

com_instance.Quit()

这篇关于使用Python将宏注入电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！