[Python]如何使用Python提取Excel VBA代码并在VSCode中提取代码（如果XVBA不起作用，则为可选）

📅 发布日: 2025/06/02, 更新日: 2025/06/02 ⏱️ 预计阅读时间约6分钟 (2,541字)

XVBA是一个Visual Studio Code扩展，允许您提取Excel VBA代码并将其另存为文件。
通过将VBA代码保存为文件，可以更轻松地管理代码更改并使用GitHub Copilot完成代码。
但是，当我尝试在我的环境中使用XVBA时，遇到了一个问题，即在将VBA代码写回文件后无法打开Excel文件。在本文中，我将介绍一种在XVBA无法正常工作时使用Python提取Excel VBA代码的方法。

编码规则和先决条件
（先总结）提取/编写Excel VBA代码的Python脚本
Python 脚本说明
1. sync_vba.py的结构
常数说明
功能说明
（额外）如何在保存时自动将 VBA 代码写回 Excel
1. .vscode/扩展.json
2. .vscode/设置.json

编码规则和先决条件

本文中的Python脚本遵循PEP8。
使用 flake8 检查语法，并使用黑色完成自动格式化。
对于因处理原因需要跳过检查的代码，请添加“#noqa”。
最大行长度 ( max-line-length ) 设置为每行 200 个字符。
该脚本已使用 Python 3.13.3 进行测试。
尚未在 macOS 上验证操作。虽然它可以在安装了 Office for Mac 的环境中运行，但 此脚本适用于 Windows 。
以下扩展用于在 Visual Studio Code 中编辑 VBA：
VBA - Visual Basic 应用程序
将以下内容添加到“.vscode/extensions.json”，以便在 Visual Studio Code 中打开 VBA 文件时自动启用 VBA 扩展：

{
    "recommendations": [
        "serkonda7.vscode-vba"
    ]
}

将以下设置添加到 Visual Studio Code settings.json 文件中，以使用 Shift-JIS 编码打开 VBA 文件。
- Python 和 JSON 文件设置为使用 UTF-8 编码打开。

{
    "files.encoding": "shiftjis",
    "[python]": {
        "files.encoding": "utf8"
    },
    "[json]": {
        "files.encoding": "utf8"
    }
}

（先总结）提取/编写Excel VBA代码的Python脚本

以下 Python 脚本从 Excel 文件中提取 VBA 代码并将其写入其中。使用适当的文件名保存它（例如“sync_vba.py”）。

import argparse
import difflib
import json
import os
import shutil
import sys
import zipfile
from datetime import datetime

import openpyxl
import xlwings as xw
from oletools.olevba import VBA_Parser

CONFIG_FILE = "config.json"
CACHE_DIR = ".cache"
BACKUP_KEEP_COUNT = 3 


def create_config():
    config = {"xlsm_file_path": "your_xlsm_file_path_here.xlsm"}
    with open(CONFIG_FILE, "w", encoding="utf-8") as f:
        json.dump(config, f, indent=4, ensure_ascii=False)
    print(f"{CONFIG_FILE} has been created. Please edit xlsm_file_path.")


def load_config():
    if not os.path.exists(CONFIG_FILE):
        print(
            f"{CONFIG_FILE} not found. Please initialize with the --init option."
        )
        sys.exit(1)
    with open(CONFIG_FILE, "r", encoding="utf-8") as f:
        return json.load(f)


def extract_vba(xlsm_file_path, output_dir="vba", force_master=False):
    if not os.path.exists(xlsm_file_path):
        sys.exit(1)
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)
    if not os.path.exists(CACHE_DIR):
        os.makedirs(CACHE_DIR)
    with zipfile.ZipFile(xlsm_file_path, "r") as z:
        vba_bin_path = None
        for f in z.namelist():
            if f == "xl/vbaProject.bin":
                vba_bin_path = f
                break
        if not vba_bin_path:
            sys.exit(1)
        tmp_bin = os.path.join(CACHE_DIR, "vbaProject.bin")
        with z.open(vba_bin_path) as src, open(tmp_bin, "wb") as dst:
            dst.write(src.read())
    vba_parser = VBA_Parser(tmp_bin)
    if not vba_parser.detect_vba_macros():
        return
    for (filename, stream_path, vba_filename, vba_code) in vba_parser.extract_macros():
        if vba_code:
            filtered_code = "\n".join(
                line for line in vba_code.splitlines() if not line.strip().startswith("Attribute VB_")
            )
            cache_path = os.path.join(CACHE_DIR, vba_filename)
            with open(cache_path, "w", encoding="shift-jis") as f:
                f.write(filtered_code)
            vba_path = os.path.join(output_dir, vba_filename)
            if os.path.exists(vba_path):
                try:
                    with open(vba_path, "r", encoding="shift-jis") as f1:
                        old = f1.readlines()
                except UnicodeDecodeError:
                    with open(vba_path, "r", encoding="utf-8") as f1:
                        content = f1.read()
                    with open(vba_path, "w", encoding="shift-jis") as f1:
                        f1.write(content)
                    old = content.splitlines(keepends=True)
                with open(cache_path, "r", encoding="shift-jis") as f2:
                    new = f2.readlines()
                diff = list(difflib.unified_diff(old, new, fromfile=vba_path, tofile=cache_path))
                if diff:
                    print(f"\nDiff for {vba_filename}:")
                    print("")
                    print("".join(diff))
                    print("")
                    print("* + : New content extracted from xlsm file, - : Content in current file)")
                    if force_master:
                        print(f"--force option: {vba_filename} will keep the current file as master.")
                        continue
                    while True:
                        sel = input(f"Difference found in {vba_filename}. Which do you want to keep as master? [k] Keep current file/[n] Overwrite with new extracted content > ").strip().lower()
                        if sel == "k":
                            break
                        elif sel == "n":
                            with open(vba_path, "w", encoding="shift-jis") as f:
                                f.write(filtered_code)
                            break
            else:
                with open(vba_path, "w", encoding="shift-jis") as f:
                    f.write(filtered_code)


def write_vba_to_xlsm(xlsm_file_path, vba_dir="vba"):
    abs_path = os.path.abspath(xlsm_file_path)
    if not os.path.exists(abs_path):
        print("The specified xlsm file does not exist. Please check the path.")
        sys.exit(1)
    app = xw.App(visible=False)
    try:
        wb = xw.Book(abs_path)
        vba_modules = {mod.Name: mod for mod in wb.api.VBProject.VBComponents}
        for fname in os.listdir(vba_dir):
            if fname.startswith("."):
                continue
            fpath = os.path.join(vba_dir, fname)
            if not os.path.isfile(fpath):
                continue
            mod_name, ext = os.path.splitext(fname)
            import re
            if not re.match(r"^[A-Za-z_][A-Za-z0-9_]*$", mod_name):
                print(f"Invalid module name: {mod_name} (Must start with a letter or _, and only contain alphanumeric and _)")
                sys.exit(1) 
            if mod_name in vba_modules:
                with open(fpath, "r", encoding="shift-jis") as f:
                    code = f.read()
                code_module = vba_modules[mod_name].CodeModule
                code_module.DeleteLines(1, code_module.CountOfLines)
                code_module.AddFromString(code)
            else:
                if ext.lower() == ".bas":
                    kind = 1
                elif ext.lower() == ".cls":
                    kind = 2
                elif ext.lower() == ".frm":
                    kind = 3
                else:
                    continue
                with open(fpath, "r", encoding="shift-jis") as f:
                    code = f.read()
                mod = wb.api.VBProject.VBComponents.Add(kind)
                mod.Name = mod_name
                mod.CodeModule.AddFromString(code)
        wb.save()
        wb.close()
    except Exception as e:
        print("Error opening workbook:", e)
        raise
    finally:
        app.quit()


def backup_xlsm(xlsm_file_path):
    if not os.path.exists(CACHE_DIR):
        os.makedirs(CACHE_DIR)
    base = os.path.basename(xlsm_file_path)
    dt = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_name = f"{base}.{dt}.bak"
    backup_path = os.path.join(CACHE_DIR, backup_name)
    shutil.copy2(xlsm_file_path, backup_path)
    backups = sorted(
        [f for f in os.listdir(CACHE_DIR) if f.startswith(base) and f.endswith(".bak")],
        reverse=True
    )
    for old in backups[BACKUP_KEEP_COUNT:]:
        try:
            os.remove(os.path.join(CACHE_DIR, old))
        except Exception:
            pass


def main(force_master=False):
    config = load_config()
    xlsm_file_path = config.get("xlsm_file_path")
    if not xlsm_file_path:
        print("xlsm_file_path is not set in config.json.")
        sys.exit(1)
    backup_xlsm(xlsm_file_path)
    extract_vba(xlsm_file_path, force_master=force_master)
    write_vba_to_xlsm(xlsm_file_path)
    print("Completed successfully.")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description="VBA module sync tool"
    )
    parser.add_argument("--init", action="store_true", help="Initialize config.json.")
    parser.add_argument("--force", action="store_true", help="Keep existing vba files as master even if there are differences.")
    args = parser.parse_args()

    if args.init:
        create_config()
        sys.exit(0)
    main(force_master=args.force)

Python 脚本中使用以下模块：
标准库：
argparse ：命令行参数解析
difflib ：显示差异
json ：读取和写入配置文件
os 、 shutil 、 sys 、 zipfile ：文件操作
datetime ：日期和时间操作
外部库：
openpyxl ：读取和写入 Excel 文件
xlwings ：操作 Excel VBA
oletools ：提取 VBA 代码
要安装所需的模块，请运行以下命令：

pip install openpyxl xlwings oletools

对于初始设置，使用以下命令生成“config.json”：

python sync_vba.py --init

在编辑器中打开生成的“config.json”，并将“xlsm_file_path”的值更改为要从中提取 VBA 代码的 Excel 文件的路径。

{
    "xlsm_file_path": "your_xlsm_file_path_here.xlsm"
}

之后，运行以下命令来提取并写入VBA代码：

python sync_vba.py

下面是一个执行示例。如果 Excel 文件中的 VBA 代码没有差异，则不会有输出。

> python .\sync_vba.py
Completed successfully.

如果VBA代码存在差异，则会显示差异，并提示您选择保留哪个版本作为主版本。
输入“n”覆盖当前文件。

> python .\sync_vba.py

Diff for Sheet1.cls:

--- vba\Sheet1.cls
+++ .cache\Sheet1.cls
@@ -6,6 +6,7 @@
         ActiveWindow.ScrollRow = 1
         ActiveWindow.ScrollColumn = 1
     Next ws
+    Debug.Print ("TEST")
     ThisWorkbook.Worksheets(1).Activate
     ThisWorkbook.Worksheets(1).Range("A1").Select
 End Sub

* + : New content extracted from xlsm file, - : Content in current file)
Difference found in Sheet1.cls. Which do you want to keep as master? [k] Keep current file/[n] Overwrite with new extracted content >

当指定“–force”选项时，即使与 Excel 文件中的代码存在差异，“vba”目录中的当前 VBA 文件仍将保留为主文件。

python sync_vba.py --force

运行命令后，将生成以下文件夹/文件：
您可以直接在 Visual Studio Code 中编辑“vba”目录中的 VBA 文件。修改 VBA 代码后，再次运行 sync_vba.py 将更改反映回 Excel 文件中。

# Files created as described in Coding Rules and Prerequisites
.vscode
├── extensions.json
├── settings.json
# Directory where the extracted VBA code is saved
vba
├── Sheet1.cls
├── ThisWorkbook.cls
├── Module1.bas
# Directory for temporary files (stores VBA code extracted from the Excel file and backup files)
.cache
├── Sheet1.cls
├── ThisWorkbook.cls
├── Module1.bas
├── your_xlsm_file_path_here.xlsm.${datetime}.bak
# Configuration file generated with the --init option
config.json
# Main Python script for extracting/writing VBA code
sync_vba.py

Python 脚本说明

sync_vba.py的结构

sync_vba.py 是一个从 Excel 文件中提取 VBA 代码并将其保存到指定目录的脚本。
该脚本提供以下功能：
使用“–init”选项生成初始配置文件“config.json”
当使用“–force”选项发现差异时，将当前文件保留为主文件
从Excel文件中提取VBA代码并将其保存到指定目录
将提取的 VBA 代码写回 Excel 文件
当发现差异时，提示用户选择保留哪个版本作为主版本
该脚本按顺序处理以下步骤：

读取 config.json 文件并获取Excel文件的路径
创建 Excel 文件的备份
从Excel文件中提取VBA代码并将其保存到指定目录
将提取的 VBA 代码写回 Excel 文件

常数说明

Python 脚本中使用以下常量。请根据您的用例的需要修改它们。
CONFIG_FILE ：配置文件的名称。保存为“config.json”。
CACHE_DIR ：存储临时文件的目录。保存为“.cache”。
BACKUP_KEEP_COUNT ：要保留的备份文件的数量。将保留最多此数量的最新备份（包括最新备份）。

功能说明

创建配置（）

生成配置文件“config.json”。
在第一次执行期间指定 --init 选项时调用此函数。

def create_config():
    config = {"xlsm_file_path": "your_xlsm_file_path_here.xlsm"}
    with open(CONFIG_FILE, "w", encoding="utf-8") as f:
        json.dump(config, f, indent=4, ensure_ascii=False)
    print(f"{CONFIG_FILE} has been created. Please edit xlsm_file_path.")

加载配置()

加载配置文件“config.json”并将其作为字典返回。
如果配置文件不存在，则显示错误消息并退出。

def load_config():
    if not os.path.exists(CONFIG_FILE):
        print(
            f"{CONFIG_FILE} not found. Please initialize with the --init option."
        )
        sys.exit(1)
    with open(CONFIG_FILE, "r", encoding="utf-8") as f:
        return json.load(f)

extract_vba（xlsm_file_path，output_dir =“vba”，force_master = False）

从指定的Excel文件中提取VBA代码并将其保存到指定的目录。
xlsm_file_path ：目标 Excel 文件的路径
output_dir ：保存提取的 VBA 代码的目录（默认为 vba ）
force_master : 当发现差异时是否保留当前文件作为主文件(默认为 False )
如果 Excel 文件不存在，则会显示错误消息并退出进程。
提取的VBA代码保存在 output_dir 目录中。
如果存在差异，系统会提示用户选择保留哪个版本作为主版本。

def extract_vba(xlsm_file_path, output_dir="vba", force_master=False):
    if not os.path.exists(xlsm_file_path):
        sys.exit(1)
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)
    if not os.path.exists(CACHE_DIR):
        os.makedirs(CACHE_DIR)
    with zipfile.ZipFile(xlsm_file_path, "r") as z:
        vba_bin_path = None
        for f in z.namelist():
            if f == "xl/vbaProject.bin":
                vba_bin_path = f
                break
        if not vba_bin_path:
            sys.exit(1)
        tmp_bin = os.path.join(CACHE_DIR, "vbaProject.bin")
        with z.open(vba_bin_path) as src, open(tmp_bin, "wb") as dst:
            dst.write(src.read())
    vba_parser = VBA_Parser(tmp_bin)
    if not vba_parser.detect_vba_macros():
        return
    for (filename, stream_path, vba_filename, vba_code) in vba_parser.extract_macros():
        if vba_code:
            filtered_code = "\n".join(
                line for line in vba_code.splitlines() if not line.strip().startswith("Attribute VB_")
            )
            cache_path = os.path.join(CACHE_DIR, vba_filename)
            with open(cache_path, "w", encoding="shift-jis") as f:
                f.write(filtered_code)
            vba_path = os.path.join(output_dir, vba_filename)
            if os.path.exists(vba_path):
                try:
                    with open(vba_path, "r", encoding="shift-jis") as f1:
                        old = f1.readlines()
                except UnicodeDecodeError:
                    with open(vba_path, "r", encoding="utf-8") as f1:
                        content = f1.read()
                    with open(vba_path, "w", encoding="shift-jis") as f1:
                        f1.write(content)
                    old = content.splitlines(keepends=True)
                with open(cache_path, "r", encoding="shift-jis") as f2:
                    new = f2.readlines()
                diff = list(difflib.unified_diff(old, new, fromfile=vba_path, tofile=cache_path))
                if diff:
                    print(f"\nDiff for {vba_filename}:")
                    print("")
                    print("".join(diff))
                    print("")
                    print("* + : New content extracted from xlsm file, - : Content in current file)")
                    if force_master:
                        print(f"--force option: {vba_filename} will keep the current file as master.")
                        continue
                    while True:
                        sel = input(f"Difference found in {vba_filename}. Which do you want to keep as master? [k] Keep current file/[n] Overwrite with new extracted content > ").strip().lower()
                        if sel == "k":
                            break
                        elif sel == "n":
                            with open(vba_path, "w", encoding="shift-jis") as f:
                                f.write(filtered_code)
                            break
            else:
                with open(vba_path, "w", encoding="shift-jis") as f:
                    f.write(filtered_code)

write_vba_to_xlsm(xlsm_file_path, vba_dir=“vba”)

从指定目录读取VBA代码并将其写回指定的Excel文件。
xlsm_file_path ：目标 Excel 文件的路径
vba_dir ：存储 VBA 代码的目录（默认为 vba ）
如果 Excel 文件不存在，则会显示错误消息并退出进程。
VBA 代码从“vba_dir”目录读取并写回 Excel 文件。

def write_vba_to_xlsm(xlsm_file_path, vba_dir="vba"):
    abs_path = os.path.abspath(xlsm_file_path)
    if not os.path.exists(abs_path):
        print("The specified xlsm file does not exist. Please check the path.")
        sys.exit(1)
    app = xw.App(visible=False)
    try:
        wb = xw.Book(abs_path)
        vba_modules = {mod.Name: mod for mod in wb.api.VBProject.VBComponents}
        for fname in os.listdir(vba_dir):
            if fname.startswith("."):
                continue
            fpath = os.path.join(vba_dir, fname)
            if not os.path.isfile(fpath):
                continue
            mod_name, ext = os.path.splitext(fname)
            import re
            if not re.match(r"^[A-Za-z_][A-Za-z0-9_]*$", mod_name):
                print(f"Invalid module name: {mod_name} (Must start with a letter or _, and only contain alphanumeric and _)")
                sys.exit(1) 
            if mod_name in vba_modules:
                with open(fpath, "r", encoding="shift-jis") as f:
                    code = f.read()
                code_module = vba_modules[mod_name].CodeModule
                code_module.DeleteLines(1, code_module.CountOfLines)
                code_module.AddFromString(code)
            else:
                if ext.lower() == ".bas":
                    kind = 1
                elif ext.lower() == ".cls":
                    kind = 2
                elif ext.lower() == ".frm":
                    kind = 3
                else:
                    continue
                with open(fpath, "r", encoding="shift-jis") as f:
                    code = f.read()
                mod = wb.api.VBProject.VBComponents.Add(kind)
                mod.Name = mod_name
                mod.CodeModule.AddFromString(code)
        wb.save()
        wb.close()
    except Exception as e:
        print("Error opening workbook:", e)
        raise
    finally:
        app.quit()

backup_xlsm(xlsm_文件路径)

创建指定 Excel 文件的备份。
备份文件保存在“.cache”目录中，并保留最多“BACKUP_KEEP_COUNT”的最新备份。
备份文件名是通过将当前日期和时间附加到原始文件名来生成的。

def backup_xlsm(xlsm_file_path):
    if not os.path.exists(CACHE_DIR):
        os.makedirs(CACHE_DIR)
    base = os.path.basename(xlsm_file_path)
    dt = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_name = f"{base}.{dt}.bak"
    backup_path = os.path.join(CACHE_DIR, backup_name)
    shutil.copy2(xlsm_file_path, backup_path)
    backups = sorted(
        [f for f in os.listdir(CACHE_DIR) if f.startswith(base) and f.endswith(".bak")],
        reverse=True
    )
    for old in backups[BACKUP_KEEP_COUNT:]:
        try:
            os.remove(os.path.join(CACHE_DIR, old))
        except Exception:
            pass

主要（force_master=False）

执行脚本的主要过程。
force_master : 当发现差异时是否保留当前文件作为主文件（默认为 False ）。
加载配置文件并检索 Excel 文件的路径。
创建 Excel 文件的备份、提取 VBA 代码并将其写回。
如果该过程成功完成，则会显示完成消息。

def main(force_master=False):
    config = load_config()
    xlsm_file_path = config.get("xlsm_file_path")
    if not xlsm_file_path:
        print("xlsm_file_path is not set in config.json.")
        sys.exit(1)
    backup_xlsm(xlsm_file_path)
    extract_vba(xlsm_file_path, force_master=force_master)
    write_vba_to_xlsm(xlsm_file_path)
    print("Completed successfully.")

切入点

直接执行脚本时的入口点是 if __name__ == "__main__": 块。
argparse 模块用于解析命令行参数。
--init ：生成配置文件的选项。
--force ：当发现差异时保留当前文件作为主文件的选项。
解析命令行参数后，如果指定了 --init 选项，则会生成配置文件。
否则，调用 main() 函数来执行进程。

if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description="VBA module sync tool"
    )
    parser.add_argument("--init", action="store_true", help="Initialize config.json.")
    parser.add_argument("--force", action="store_true", help="Keep existing vba files as master even if there are differences.")
    args = parser.parse_args()

    if args.init:
        create_config()
        sys.exit(0)
    main(force_master=args.force)

（额外）如何在保存时自动将 VBA 代码写回 Excel

您可以将 Visual Studio Code 配置为在保存 VBA 文件时自动运行 Python 脚本并将 VBA 代码写回 Excel 文件。
通过使用 Visual Studio Code 的“保存时运行”扩展，您可以在保存文件时自动执行命令。
要使用此扩展，请在工作区（Excel 文件所在的目录）中创建以下两个文件。

.vscode/扩展.json

这是 Visual Studio Code 的推荐扩展文件。将以下内容保存为“.vscode/extensions.json”。
当您打开 VBA 文件时，VBA 扩展和“保存时运行”扩展将自动启用。

{
    "recommendations": [
        "emeraldwalk.RunOnSave"
    ]
}

.vscode/设置.json

这是 Visual Studio Code 设置文件。将以下内容保存为“.vscode/settings.json”。
当您保存 VBA 文件时，此配置将自动执行 Python 脚本。

{
    "emeraldwalk.runonsave": {
        "commands": [
            {
                "match": ".*\\.(cls|bas)$",
                "isAsync": false,
                "cmd": "python .\\sync_vba.py --force"
            }
        ]
    }
}