目的:对Office的VBA进行Command Line式的批处理。
环境:Window XP、Python 2.5、Excel 2003
步骤:
Step1: 新建待试验Excel文件。确认Excel的安全级别非“高”,确保Excel的宏可以运行。在VBE环境下,插入一个模块Module1(或者在Sheet操作区域中插入Public的Sub),保存Excel,命名为testExcel.xls,退出。代码如下:
Sub
PythonToExcel()
Dim finalRow as Integer
finalRow = Range( " A6635 " ).End(xlup).Row
Sheets( 1 ).Cells(finalRow, 1 ) = " Log Time " & " : " & Now ()
End Sub
Dim finalRow as Integer
finalRow = Range( " A6635 " ).End(xlup).Row
Sheets( 1 ).Cells(finalRow, 1 ) = " Log Time " & " : " & Now ()
End Sub
Step2:使用Phython IDE,在测试目录下新建调用Excel的脚本程序,保存Python程序,命名为testPython.py,Debug,Run Module,退出。代码(改编自http://www.windowsdevcenter.com/pub/a/windows/2005/03/22/word_macros.html#picking_scripting_language)如下:
#
batchmacro.py
# Python script for batch running Word macros
import sys
import os
import glob
from win32com.client import Dispatch
# Launch new instance of Word
wb = Dispatch( ' Excel.Application ' )
wb.Visible = 1
# First argument to script is the name of the macro
macro_to_run = sys.argv[ 1 ]
# Everything else is a document on which to run macro
for arg in sys.argv[ 2 :]:
# Expand any wildcards that might be in the argument
for file in glob.glob(arg):
doc = wb.Workbooks.Open(os.path.abspath(file))
wb.Run(macro_to_run)
doc.Save()
doc.Close()
wb.Quit()
# Python script for batch running Word macros
import sys
import os
import glob
from win32com.client import Dispatch
# Launch new instance of Word
wb = Dispatch( ' Excel.Application ' )
wb.Visible = 1
# First argument to script is the name of the macro
macro_to_run = sys.argv[ 1 ]
# Everything else is a document on which to run macro
for arg in sys.argv[ 2 :]:
# Expand any wildcards that might be in the argument
for file in glob.glob(arg):
doc = wb.Workbooks.Open(os.path.abspath(file))
wb.Run(macro_to_run)
doc.Save()
doc.Close()
wb.Quit()
Step3:cmd-〉进入你的Python目录-〉键入命令
python your
path
/
testPython
.
py PythonToExcel *
.
doc
结果:Python脚本处理了Office文档。