Python Inject VBA Macro into XLSM File

Introduction

雖然在 Python 中可以通過 openpyxl 套件來很好的操作 Excel 讀寫,但當想要在檔案中插入 VBA macro 時便沒有辦法依靠 openpyxl。不過我們仍然可以透過 pywin32 套件來操作 Windows API,達到在 Excel 檔案中插入 VBA macro 的需求。

在本篇文章中將會提到:

  1. 透過 pywin32 對 XLSM 檔案插入 VBA macro。
  2. 解決在過程中會出現的「檔案信任」的問題。
  3. 將程式碼包裝為類(class)以方便使用,以及其使用範例。
  4. 如何透過 openpyxl 建立可插入 VBA macro 的 XLSM 檔案。

Steps

Install pywin32 Package

1
pip3 install pywin32

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from win32com.client import Dispatch


INPUT_FILE = 'The ABSOLITE file path of input xlsm file'
OUTPUT_FILE = 'The ABSOLITE file path of output xlsm file'
MACRO = 'The VBA macro'


if __name__ == '__main__':
com_instance = Dispatch("Excel.Application")
com_instance.Visible = True
com_instance.DisplayAlerts = False

objworkbook = com_instance.Workbooks.Open(INPUT_FILE)
# Inject into the `ThisWorkbook`
xlmodule = self.objworkbook.VBProject.VBComponents('ThisWorkbook')
xlmodule.CodeModule.AddFromString(MACRO.strip())
# Inject into the module
xlmodule = self.objworkbook.VBProject.VBComponents.Add(1)
xlmodule.CodeModule.AddFromString(MACRO.strip())

objworkbook.SaveAs(OUTPUT_FILE)
com_instance.Quit()

需要特別注意的是,餵給 com_instance.Workbooks.Open 的檔案路徑必須是絕對路徑,若是相對路徑將會引發錯誤。舉例而言,當我們餵入 ./vba_test_src.xlsm 會得到這樣的結果:

1
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Sorry, we couldn't find ./vba_test_src.xlsm. Is it possible it was moved, renamed or deleted?", 'xlmain11.chm', 0, -2146827284), None)

因此我們可以通過 os package 來將路徑轉換為絕對路徑後再餵入:

1
2
3
4
import os


objworkbook = com_instance.Workbooks.Open(os.path.abspath('./vba_test_src.xlsm'))

Programmatic Access

若依照上述範例中的程式碼直接執行,將會引發錯誤:

1
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Programmatic access to Visual Basic Project is not trusted\n', 'xlmain11.chm', 0, -2146827284), None)

這是因為出於安全原因,在預設情況下這樣的行為(對 Visual Basic Project 的程式性存取)是被禁止的,因此我們需要信任文件來讓我們程式可以順利執行。接下來將說明如何解決這樣的問題。

Access by Editing File Settings

開啟檔案,並前往 File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> activate checkmark on Trust access to the VBA project object model.

雖然這項解法只需要勾選選項並進行文件信任,但每個檔案都需要在執行程式前先設定過,為了使程式能夠更自動化的執行,我們可以通過另外一種解法來達到。

Access by Code

1
$ pip3 install pywin32
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import winreg


def set_access_vbom():
reg_path = r'Software\Microsoft\Office\16.0\Excel\Security'
reg_name = 'AccessVBOM'

try:
winreg.CreateKey(winreg.HKEY_CURRENT_USER, reg_path)
registry_key = winreg.OpenKey(winreg.HKEY_CURRENT_USER, reg_path, 0, winreg.KEY_WRITE)
winreg.SetValueEx(registry_key, name, 1, winreg.REG_DWORD, value)
winreg.CloseKey(registry_key)
return True
except WindowsError:
return False

set_access_vbom()

Solution

最後可以將上面的範例整合組合成一個 Python class (GitHub: ychsieh95/Python-VBA-Writer),使我們更有效方便的去使用它:

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from vba_macro_writer import VbaMacroWriter


INPUT_FILE = 'The ABSOLITE file path of input xlsm file'
STARTUP_MACRO = 'The VBA startup macro'
MODULE_MACRO = 'The VBA module macro'


if __name__ == '__main__':
m_writer = VbaWriter(INPUT_FILE)
if not m_writer.check_reg_accessable():
m_writer.write_reg_accessable()
m_writer.open_file()
m_writer.write_macro_workbook_from_text(STARTUP_MACRO)
m_writer.write_macro_module_from_text(MODULE_MACRO)
m_writer.save_file()

Remark

Create XLSM File by openpyxl

若需要在 openpyxl 中建立一個新的 Workbook 並在中插入 VBA macro,需要先將檔案儲存為 .xlsm 並重新儲存:

1
2
3
4
5
6
7
8
9
10
11
12
13
import openpyxl


DST_FILEPATH = './example.xlsm'


# Create a workbook and save it
wb = Workbook()
wb.save(filename=DST_FILEPATH)

# Re-save it
wb = openpyxl.load_workbook(DST_FILEPATH , read_only=False, keep_vba=True)
wb.save(filename=DST_FILEPATH)

在儲存後再透過前述方法插入 VBA macro。

References