[Python] Cómo extraer código Excel VBA con Python y código en VSCode (Alternativa si XVBA no funciona)

📅 Creado: 2025/06/02, Actualizado: 2025/06/02 ⏱️ Tiempo estimado de lectura ~6 mins (1,123 palabras)

XVBA is a Visual Studio Code extension that allows you to extract Excel VBA code and save it as files.
By saving VBA code as files, it becomes easier to manage code changes and use GitHub Copilot for code completion.
However, when I tried to use XVBA in my environment, I encountered an issue where the Excel file could not be opened after writing the VBA code back to the file.In this article, I will introduce a method to extract Excel VBA code using Python as an alternative when XVBA does not work properly.

Índice

Coding Rules and Prerequisites
(Summary First) Python Script to Extract/Write Excel VBA Code
Explanation of the Python Script
1. Structure of sync_vba.py
Description of Constants
Function Descriptions
(Bonus) How to Automatically Write Back VBA Code to Excel When Saving
1. .vscode/extensions.json
2. .vscode/settings.json

Coding Rules and Prerequisites

The Python script in this article follows PEP8.
Syntax is checked with flake8, and automatic formatting is done with black.
For code that needs to skip checks for processing reasons, add # noqa .
The maximum line length ( max-line-length ) is set to 200 characters per line.
The script has been tested with Python 3.13.3.
Operation has not been verified on macOS. While it may work in environments with Office for Mac installed, this script is intended for use on Windows .
The following extension is used to edit VBA in Visual Studio Code:
VBA - Visual Basic for Applications
Add the following to .vscode/extensions.json to automatically enable the VBA extension when opening VBA files in Visual Studio Code:

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

Add the following settings to your Visual Studio Code settings.json file to open VBA files with Shift-JIS encoding.
- Python and JSON files are set to open with UTF-8 encoding.

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

(Summary First) Python Script to Extract/Write Excel VBA Code

The following Python script extracts and writes VBA code from/to Excel files. Save it with an appropriate filename (e.g., 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)

The following modules are used in the Python script:
Standard libraries:
argparse : Command-line argument parsing
difflib : Displaying differences
json : Reading and writing configuration files
os , shutil , sys , zipfile : File operations
datetime : Date and time operations
External libraries:
openpyxl : Reading and writing Excel files
xlwings : Manipulating Excel VBA
oletools : Extracting VBA code
To install the required modules, run the following command:

pip install openpyxl xlwings oletools

For the initial setup, generate config.json with the following command:

python sync_vba.py --init

Open the generated config.json in your editor and change the value of xlsm_file_path to the path of the Excel file from which you want to extract VBA code.

{
    "xlsm_file_path": "your_xlsm_file_path_here.xlsm"
}

After that, run the following command to extract and write VBA code:

python sync_vba.py

Below is an example of execution. If there are no differences in the VBA code within the Excel file, there will be no output.

> python .\sync_vba.py
Completed successfully.

If there are differences in the VBA code, the differences will be displayed and you will be prompted to choose which version to keep as the master.
Enter n to overwrite the current file.

> 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 >

When the --force option is specified, the current VBA files in the vba directory are kept as the master even if there are differences with the code in the Excel file.

python sync_vba.py --force

After running the command, the following folders/files will be generated:
You can edit the VBA files in the vba directory directly in Visual Studio Code. After modifying the VBA code, run sync_vba.py again to reflect the changes back into the Excel file.

# 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

Explanation of the Python Script

Structure of sync_vba.py

sync_vba.py is a script that extracts VBA code from an Excel file and saves it to a specified directory.
The script provides the following features:
Generates the initial configuration file config.json with the --init option
Keeps the current files as master when differences are found by using the --force option
Extracts VBA code from the Excel file and saves it to the specified directory
Writes the extracted VBA code back to the Excel file
When differences are found, prompts the user to choose which version to keep as the master
The script processes the following steps in order:

Reads the config.json file and obtains the path to the Excel file
Creates a backup of the Excel file
Extracts VBA code from the Excel file and saves it to the specified directory
Writes the extracted VBA code back to the Excel file

Description of Constants

The following constants are used in the Python script. Please modify them as needed for your use case.
CONFIG_FILE : The name of the configuration file. Saved as config.json .
CACHE_DIR : The directory for storing temporary files. Saved as .cache .
BACKUP_KEEP_COUNT : The number of backup files to keep. Up to this number of the most recent backups (including the latest) will be retained.

Function Descriptions

create_config()

Generates the configuration file config.json .
This function is called when the --init option is specified during the first execution.

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.")

load_config()

Loads the configuration file config.json and returns it as a dictionary.
If the configuration file does not exist, displays an error message and exits.

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)

Extracts VBA code from the specified Excel file and saves it to the specified directory.
xlsm_file_path : Path to the target Excel file
output_dir : Directory to save the extracted VBA code (default is vba )
force_master : Whether to keep the current file as the master when differences are found (default is False )
If the Excel file does not exist, an error message is displayed and the process exits.
The extracted VBA code is saved in the output_dir directory.
If there are differences, the user is prompted to choose which version to keep as the master.

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”)

Reads VBA code from the specified directory and writes it back to the specified Excel file.
xlsm_file_path : Path to the target Excel file
vba_dir : Directory where the VBA code is stored (default is vba )
If the Excel file does not exist, an error message is displayed and the process exits.
VBA code is read from the vba_dir directory and written back to the Excel file.

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_file_path)

Creates a backup of the specified Excel file.
Backup files are saved in the .cache directory, and up to BACKUP_KEEP_COUNT of the most recent backups are retained.
The backup file name is generated by appending the current date and time to the original file name.

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

main(force_master=False)

Executes the main process of the script.
force_master : Whether to keep the current file as the master when differences are found (default is False ).
Loads the configuration file and retrieves the path to the Excel file.
Creates a backup of the Excel file, extracts the VBA code, and writes it back.
If the process completes successfully, a completion message is displayed.

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.")

Entry Point

The entry point when the script is executed directly is the if __name__ == "__main__": block.
The argparse module is used to parse command-line arguments.
--init : Option to generate the configuration file.
--force : Option to keep the current file as the master when differences are found.
After parsing the command-line arguments, if the --init option is specified, the configuration file is generated.
Otherwise, the main() function is called to execute the process.

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)

(Bonus) How to Automatically Write Back VBA Code to Excel When Saving

You can configure Visual Studio Code to automatically run the Python script and write back VBA code to the Excel file whenever you save a VBA file.
By using the “Run On Save” extension for Visual Studio Code, you can execute commands automatically when files are saved.
To use this extension, create the following two files in your workspace (the directory where your Excel file is located).

.vscode/extensions.json

This is a recommended extensions file for Visual Studio Code. Save the following content as .vscode/extensions.json .
When you open a VBA file, the VBA extension and the Run On Save extension will be automatically enabled.

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

.vscode/settings.json

This is the Visual Studio Code settings file. Save the following content as .vscode/settings.json .
This configuration will execute the Python script automatically when you save a VBA file.

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