Python Inject VBA Macro into XLSM File

Posted on Edited on

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