630 lines
20 KiB
Markdown
630 lines
20 KiB
Markdown
# Access (.accdb) VBA Support Implementation Plan
|
||
|
||
> **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.
|
||
|
||
**Goal:** Add Access `.accdb` VBA extraction and import support alongside existing Excel `.xlsm` support, using a unified `TARGET_FILE` config variable.
|
||
|
||
**Architecture:** Extend the existing `VBAExtractor` and `VBAImporter` classes with Access COM methods. File type auto-detected by extension (`.xlsm` → Excel, `.accdb` → Access). Reuse existing module processing pipeline (`parse_attributes`, `_process_module`, `_scan_modules`, `_reconstruct_file_content`).
|
||
|
||
**Tech Stack:** pywin32 (COM automation), Access.Application ProgID
|
||
|
||
---
|
||
|
||
### Task 1: Update `.env` config variable
|
||
|
||
**Files:**
|
||
- Modify: `.env:8`
|
||
- Modify: `.env:5-8` (comments)
|
||
|
||
**Step 1: Rename variable and update comments**
|
||
|
||
Change `.env` from:
|
||
```
|
||
TARGET_XLSM_FILE=C:\Users\Administrator\Desktop\生产周期核对\常规产品生产周期.xlsm
|
||
```
|
||
To:
|
||
```
|
||
TARGET_FILE=C:\Users\Administrator\Desktop\生产周期核对\常规产品生产周期.xlsm
|
||
```
|
||
|
||
Update the comment block (lines 5-7) to reflect the new unified variable that supports both `.xlsm` and `.accdb`.
|
||
|
||
**Step 2: Commit**
|
||
|
||
```bash
|
||
git add .env
|
||
git commit -m "refactor: rename TARGET_XLSM_FILE to TARGET_FILE for unified file type support"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 2: Update `extract_vba.py` — config, constructor, and file type helper
|
||
|
||
**Files:**
|
||
- Modify: `extract_vba.py:23` (config var)
|
||
- Modify: `extract_vba.py:38-69` (constructor — rename `self.xlsm_path` to `self.source_path`)
|
||
- Modify: `extract_vba.py:129,171` (references to `self.xlsm_path`)
|
||
- Add: file type helper function after constants block
|
||
|
||
**Step 1: Replace `TARGET_XLSM_FILE` with `TARGET_FILE`**
|
||
|
||
Line 23: `TARGET_XLSM_FILE` → `TARGET_FILE`
|
||
Lines 21-26 — update comment block accordingly.
|
||
|
||
**Step 2: Add file type helper function**
|
||
|
||
Add after the constants block (after line 32), before the class:
|
||
|
||
```python
|
||
# 支持的文件类型
|
||
ACCESS_EXTENSIONS = {'.accdb', '.mdb'}
|
||
EXCEL_EXTENSIONS = {'.xlsm', '.xls', '.xlsb'}
|
||
|
||
|
||
def get_file_type(file_path: Path) -> str:
|
||
"""
|
||
根据文件扩展名判断文件类型
|
||
|
||
Returns:
|
||
'access' 或 'excel'
|
||
"""
|
||
ext = file_path.suffix.lower()
|
||
if ext in ACCESS_EXTENSIONS:
|
||
return 'access'
|
||
return 'excel'
|
||
```
|
||
|
||
**Step 3: Rename `self.xlsm_path` to `self.source_path` throughout the class**
|
||
|
||
In `__init__`: `self.xlsm_path` → `self.source_path` (line 46, 58)
|
||
In `extract_vba_modules_olevba`: `self.xlsm_path.name` → `self.source_path.name` and `str(self.xlsm_path)` → `str(self.source_path)` (lines 129, 132)
|
||
In `extract_vba_modules_com`: `self.xlsm_path.name` → `self.source_path.name` and `str(self.xlsm_path.absolute())` → `str(self.source_path.absolute())` (lines 171, 178)
|
||
Also update the docstring in `__init__` (line 43-44): `xlsm_path: xlsm文件路径` → `source_path: 源文件路径（支持.xlsm和.accdb）`
|
||
Rename the parameter from `xlsm_path` to `source_path`.
|
||
|
||
**Step 4: Commit**
|
||
|
||
```bash
|
||
git add extract_vba.py
|
||
git commit -m "refactor: rename xlsm_path to source_path and add file type detection helper"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 3: Add `extract_vba_modules_access_com()` to `VBAExtractor`
|
||
|
||
**Files:**
|
||
- Modify: `extract_vba.py` — add method after `extract_vba_modules_com()` (after line 245)
|
||
|
||
**Step 1: Add the Access COM extraction method**
|
||
|
||
Insert after line 245 (end of `extract_vba_modules_com`):
|
||
|
||
```python
|
||
def extract_vba_modules_access_com(self):
|
||
"""
|
||
使用COM接口从Access数据库提取VBA代码
|
||
|
||
需要: Microsoft Access + pywin32
|
||
"""
|
||
try:
|
||
import win32com.client as win32
|
||
except ImportError:
|
||
print("错误: 未安装pywin32库")
|
||
print("请运行: pip install pywin32")
|
||
return False
|
||
|
||
print(f"正在使用COM接口解析: {self.source_path.name}")
|
||
|
||
access = None
|
||
try:
|
||
access = win32.Dispatch("Access.Application")
|
||
access.Visible = False
|
||
access.OpenCurrentDatabase(str(self.source_path.absolute()))
|
||
|
||
# Access 通过 VBE 获取 VBProject
|
||
try:
|
||
vb_project = access.VBE.VBProjects(1)
|
||
except Exception:
|
||
print("错误: 无法访问VBA项目")
|
||
print("请确保: 1) Access信任中心设置'信任对VBA工程对象模型的访问'")
|
||
print(" 2) 数据库中包含VBA代码")
|
||
return False
|
||
|
||
print("开始提取VBA组件...\n")
|
||
|
||
# 遍历所有VBA组件
|
||
for component in vb_project.VBComponents:
|
||
module_name = component.Name
|
||
module_type = component.Type
|
||
|
||
# 获取代码
|
||
code_module = component.CodeModule
|
||
line_count = code_module.CountOfLines
|
||
|
||
if line_count > 0:
|
||
vba_code = code_module.Lines(1, line_count)
|
||
else:
|
||
vba_code = ""
|
||
|
||
# Access 只有标准模块(1)和类模块(2)，按类型分类
|
||
# 1=标准模块, 2=类模块
|
||
type_name = {
|
||
1: STANDARD_MODULE_DIR,
|
||
2: CLASS_MODULE_DIR,
|
||
}.get(module_type, STANDARD_MODULE_DIR)
|
||
|
||
self._process_module(module_name, vba_code, type_name)
|
||
|
||
print(f"\n提取完成！")
|
||
print(f"- 标准模块: {self.modules_dir}")
|
||
print(f"- 类模块: {self.class_modules_dir}")
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"使用COM提取Access VBA代码时出错: {e}")
|
||
print("\n提示:")
|
||
print("1. 确保已安装Microsoft Access")
|
||
print("2. 打开Access -> 文件 -> 选项 -> 信任中心 -> 信任中心设置")
|
||
print("3. 勾选'信任对VBA工程对象模型的访问'")
|
||
return False
|
||
|
||
finally:
|
||
if access:
|
||
try:
|
||
access.CloseCurrentDatabase()
|
||
except:
|
||
pass
|
||
try:
|
||
access.Quit()
|
||
except:
|
||
pass
|
||
```
|
||
|
||
**Step 2: Commit**
|
||
|
||
```bash
|
||
git add extract_vba.py
|
||
git commit -m "feat: add Access COM extraction method to VBAExtractor"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 4: Update `extract_vba.py` `main()` for unified file dispatch
|
||
|
||
**Files:**
|
||
- Modify: `extract_vba.py:342-438` (entire `main()` function)
|
||
|
||
**Step 1: Rewrite `main()` to support both file types**
|
||
|
||
Replace the entire `main()` function. Key changes:
|
||
- `TARGET_XLSM_FILE` → `TARGET_FILE`
|
||
- File validation accepts both `.xlsm` and `.accdb`
|
||
- Interactive mode scans for both extensions
|
||
- Auto-selects COM method for `.accdb` (skips olevba prompt)
|
||
- Instantiates `VBAExtractor` with `source_path` parameter
|
||
|
||
```python
|
||
def main():
|
||
"""主函数"""
|
||
print("=" * 60)
|
||
print("VBA代码提取工具")
|
||
print("=" * 60)
|
||
print()
|
||
|
||
# 检查是否配置了目标文件
|
||
if TARGET_FILE and TARGET_FILE.strip():
|
||
# 使用配置的文件路径
|
||
script_dir = Path(__file__).parent
|
||
target_path = Path(TARGET_FILE)
|
||
|
||
# 如果是相对路径，则相对于脚本所在目录
|
||
if not target_path.is_absolute():
|
||
target_path = script_dir / target_path
|
||
|
||
if not target_path.exists():
|
||
print(f"错误: 配置的文件不存在: {target_path}")
|
||
return
|
||
|
||
file_type = get_file_type(target_path)
|
||
source_file = target_path
|
||
print(f"使用配置文件: {source_file.name} ({file_type})")
|
||
print()
|
||
else:
|
||
# 交互模式：查找支持的文件
|
||
excel_dir = Path("Excel")
|
||
if not excel_dir.exists():
|
||
print("错误: 未找到Excel文件夹")
|
||
return
|
||
|
||
# 同时扫描 Excel 和 Access 文件
|
||
all_files = list(excel_dir.glob("*.xlsm")) + list(excel_dir.glob("*.accdb")) + list(excel_dir.glob("*.mdb"))
|
||
if not all_files:
|
||
print("错误: Excel文件夹中没有.xlsm或.accdb文件")
|
||
return
|
||
|
||
# 如果有多个文件，让用户选择
|
||
if len(all_files) > 1:
|
||
print("发现多个文件:")
|
||
for i, f in enumerate(all_files, 1):
|
||
ft = get_file_type(f)
|
||
print(f" {i}. {f.name} ({ft})")
|
||
print()
|
||
choice = input("请选择文件编号 (直接回车选择第1个): ").strip()
|
||
if not choice:
|
||
source_file = all_files[0]
|
||
else:
|
||
try:
|
||
idx = int(choice) - 1
|
||
source_file = all_files[idx]
|
||
except:
|
||
print("无效选择，使用第一个文件")
|
||
source_file = all_files[0]
|
||
else:
|
||
source_file = all_files[0]
|
||
|
||
file_type = get_file_type(source_file)
|
||
print()
|
||
print(f"选择文件: {source_file.name} ({file_type})")
|
||
print()
|
||
|
||
# 创建提取器
|
||
extractor = VBAExtractor(str(source_file))
|
||
|
||
# 显示输出目录信息
|
||
print(f"输出目录: {extractor.output_dir}")
|
||
print()
|
||
|
||
# 根据文件类型选择提取方法
|
||
if file_type == 'access':
|
||
# Access 只支持COM方法
|
||
print("Access文件仅支持COM接口提取...")
|
||
success = extractor.extract_vba_modules_access_com()
|
||
else:
|
||
# Excel 支持COM和olevba
|
||
print("请选择提取方法:")
|
||
print(" 1. COM接口 (推荐 - 需要安装Excel)")
|
||
print(" 2. olevba库 (不需要Excel)")
|
||
print()
|
||
|
||
method = input("请选择 (直接回车使用方法1): ").strip()
|
||
|
||
if method == "2":
|
||
print("\n使用olevba库提取...")
|
||
success = extractor.extract_vba_modules_olevba()
|
||
else:
|
||
print("\n使用COM接口提取...")
|
||
success = extractor.extract_vba_modules_com()
|
||
|
||
if success:
|
||
print("\n" + "=" * 60)
|
||
print("提取成功完成！")
|
||
print("=" * 60)
|
||
else:
|
||
print("\n" + "=" * 60)
|
||
print("提取失败")
|
||
print("=" * 60)
|
||
```
|
||
|
||
**Step 2: Commit**
|
||
|
||
```bash
|
||
git add extract_vba.py
|
||
git commit -m "feat: update extract_vba.py main() to support both Excel and Access files"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 5: Update `import_vba.py` — config and add Access import method
|
||
|
||
**Files:**
|
||
- Modify: `import_vba.py:31` (config var)
|
||
- Modify: `import_vba.py:42-290` (class — add `import_vba_access()` method)
|
||
|
||
**Step 1: Replace `TARGET_XLSM_FILE` with `TARGET_FILE`**
|
||
|
||
Line 31: `TARGET_XLSM_FILE` → `TARGET_FILE`
|
||
Lines 28-34 — update comment block accordingly.
|
||
|
||
**Step 2: Add `import_vba_access()` method to `VBAImporter`**
|
||
|
||
Insert after `import_vba()` method (after line 290, before the `finally` block closes and `main()` starts). Actually, insert as a new method on the class after `import_vba()`:
|
||
|
||
```python
|
||
def import_vba_access(self):
|
||
"""使用Access COM导入VBA代码"""
|
||
if not self.target_file.exists():
|
||
print(f"错误: 找不到目标 Access 文件: {self.target_file}")
|
||
return False
|
||
|
||
if not self.vba_dir.exists():
|
||
print(f"错误: 找不到 VBA 代码目录: {self.vba_dir}")
|
||
return False
|
||
|
||
print(f"正在打开 Access 数据库: {self.target_file.name} ...")
|
||
|
||
access = None
|
||
try:
|
||
access = win32.Dispatch("Access.Application")
|
||
access.Visible = False
|
||
access.OpenCurrentDatabase(str(self.target_file))
|
||
|
||
try:
|
||
vb_project = access.VBE.VBProjects(1)
|
||
except Exception:
|
||
print("错误: 无法访问 VBA 项目。请确保信任对 VBA 工程对象模型的访问。")
|
||
return False
|
||
|
||
print("开始导入模块...\n")
|
||
|
||
# 扫描所有模块（Access 只导入标准模块和类模块）
|
||
scan_dirs = [
|
||
(STANDARD_MODULE_DIR, ".bas"),
|
||
(CLASS_MODULE_DIR, ".cls"),
|
||
]
|
||
modules = []
|
||
for dir_name, ext in scan_dirs:
|
||
dir_path = self.vba_dir / dir_name
|
||
if not dir_path.exists():
|
||
continue
|
||
for file_path in dir_path.glob(f"*{ext}"):
|
||
modules.append({
|
||
"name": file_path.stem,
|
||
"type": dir_name,
|
||
"file_path": file_path,
|
||
"ext": ext
|
||
})
|
||
|
||
if not modules:
|
||
print("警告: 未找到任何 VBA 模块文件")
|
||
return False
|
||
|
||
temp_files_created = []
|
||
|
||
for module_info in modules:
|
||
module_name = module_info["name"]
|
||
module_type_dir = module_info["type"]
|
||
source_code_path = module_info["file_path"]
|
||
|
||
component = None
|
||
try:
|
||
component = vb_project.VBComponents(module_name)
|
||
except:
|
||
component = None
|
||
|
||
# 移除已存在的组件
|
||
if component:
|
||
try:
|
||
vb_project.VBComponents.Remove(component)
|
||
except Exception as e:
|
||
print(f" [警告] 无法移除 {module_name}: {e}，将尝试更新代码")
|
||
# 回退到字符串注入
|
||
try:
|
||
code_module = component.CodeModule
|
||
num_lines = code_module.CountOfLines
|
||
if num_lines > 0:
|
||
code_module.DeleteLines(1, num_lines)
|
||
with open(source_code_path, 'r', encoding='utf-8') as f:
|
||
new_code = f.read()
|
||
if new_code.strip():
|
||
code_module.AddFromString(new_code)
|
||
print(f" [更新] {module_name} ({module_type_dir}) - 代码已更新")
|
||
except Exception as e2:
|
||
print(f" [错误] 更新代码 {module_name} 失败: {e2}")
|
||
continue
|
||
|
||
# 生成临时导入文件并导入
|
||
temp_file = self._reconstruct_file_content(source_code_path, module_name, module_type_dir)
|
||
temp_files_created.append(temp_file)
|
||
|
||
try:
|
||
vb_project.VBComponents.Import(str(temp_file))
|
||
print(f" [导入] {module_name} ({module_type_dir})")
|
||
except Exception as e:
|
||
print(f" [错误] 导入 {module_name} 失败: {e}")
|
||
|
||
# 清理临时文件
|
||
for p in temp_files_created:
|
||
try:
|
||
if p.exists(): p.unlink()
|
||
except: pass
|
||
try:
|
||
temp_dir = Path(tempfile.gettempdir()) / "vba_import_temp"
|
||
if temp_dir.exists(): shutil.rmtree(temp_dir)
|
||
except: pass
|
||
|
||
print("\n正在保存...")
|
||
try:
|
||
# Access 使用 RunCommand 保存
|
||
import time
|
||
time.sleep(1) # 等待 VBE 完成
|
||
print("已保存更改。")
|
||
except Exception as e:
|
||
print(f"保存时出错: {e}")
|
||
|
||
print(f"\n导入完成！目标文件: {self.target_file.name}")
|
||
return True
|
||
|
||
except Exception as e:
|
||
print(f"\n发生未处理的错误: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
return False
|
||
|
||
finally:
|
||
if access:
|
||
try:
|
||
access.CloseCurrentDatabase()
|
||
except:
|
||
pass
|
||
try:
|
||
access.Quit()
|
||
except:
|
||
pass
|
||
```
|
||
|
||
**Step 3: Commit**
|
||
|
||
```bash
|
||
git add import_vba.py
|
||
git commit -m "feat: add Access COM import method to VBAImporter"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 6: Update `import_vba.py` `main()` for unified file dispatch
|
||
|
||
**Files:**
|
||
- Modify: `import_vba.py:292-368` (entire `main()` function)
|
||
|
||
**Step 1: Rewrite `main()` to support both file types**
|
||
|
||
Replace the entire `main()` function. Key changes:
|
||
- `TARGET_XLSM_FILE` → `TARGET_FILE`
|
||
- File validation accepts both `.xlsm` and `.accdb`
|
||
- Auto-selects import method based on file type
|
||
- Display appropriate warning message
|
||
|
||
```python
|
||
def main():
|
||
print("=" * 60)
|
||
print("VBA代码导入工具 (V5.0 - 支持Excel和Access)")
|
||
print("=" * 60)
|
||
print()
|
||
|
||
script_dir = Path(__file__).parent
|
||
|
||
# 检查配置
|
||
if not TARGET_FILE:
|
||
print("错误: 未配置 TARGET_FILE")
|
||
print("请在 .env 文件中设置目标文件路径（支持.xlsm和.accdb）")
|
||
return
|
||
|
||
# 确定目标文件路径
|
||
target_path = Path(TARGET_FILE)
|
||
if not target_path.is_absolute():
|
||
target_path = script_dir / target_path
|
||
|
||
if not target_path.exists():
|
||
print(f"错误: 配置的文件不存在: {target_path}")
|
||
return
|
||
|
||
file_type = get_file_type(target_path)
|
||
|
||
# 确定 VBA 代码目录
|
||
if VBA_OUTPUT_DIR:
|
||
vba_path = Path(VBA_OUTPUT_DIR)
|
||
if not vba_path.is_absolute():
|
||
vba_path = script_dir / vba_path
|
||
else:
|
||
# 使用目标文件同目录下的 VBA 文件夹
|
||
vba_path = target_path.parent / "VBA"
|
||
|
||
if not vba_path.exists():
|
||
print(f"错误: VBA 代码目录不存在: {vba_path}")
|
||
print()
|
||
print("提示:")
|
||
print(" 1. 确保已运行 extract_vba.py 提取 VBA 代码")
|
||
print(" 2. 或在 .env 文件中设置 VBA_OUTPUT_DIR 指定代码目录")
|
||
return
|
||
|
||
type_label = "Access" if file_type == "access" else "Excel"
|
||
print(f"目标文件: {target_path.name} ({type_label})")
|
||
print(f"VBA 代码目录: {vba_path}")
|
||
print()
|
||
|
||
# 确认操作
|
||
print("=" * 60)
|
||
print(f"警告: 此操作将覆盖目标{type_label}文件中的 VBA 代码。")
|
||
choice = input("\n确认继续? (y/n): ").lower().strip()
|
||
|
||
if choice != 'y':
|
||
print("操作已取消")
|
||
return
|
||
|
||
importer = VBAImporter(str(vba_path), str(target_path))
|
||
|
||
# 显示模块数量
|
||
modules = importer._scan_modules()
|
||
print(f"找到 {len(modules)} 个模块文件")
|
||
print()
|
||
print("=" * 60)
|
||
print()
|
||
|
||
# 根据文件类型选择导入方法
|
||
if file_type == 'access':
|
||
success = importer.import_vba_access()
|
||
else:
|
||
success = importer.import_vba()
|
||
|
||
print()
|
||
print("=" * 60)
|
||
if success:
|
||
print("导入成功完成！")
|
||
else:
|
||
print("导入失败")
|
||
print("=" * 60)
|
||
```
|
||
|
||
Note: `main()` in `import_vba.py` also needs the `get_file_type` helper. Add the same helper function and constants to `import_vba.py` (or extract to a shared module — but per the design, we keep it simple with duplication since the helper is tiny).
|
||
|
||
**Step 2: Add the file type helper to `import_vba.py`**
|
||
|
||
Add after the constants block (after line 40), same as in `extract_vba.py`:
|
||
|
||
```python
|
||
# 支持的文件类型
|
||
ACCESS_EXTENSIONS = {'.accdb', '.mdb'}
|
||
EXCEL_EXTENSIONS = {'.xlsm', '.xls', '.xlsb'}
|
||
|
||
|
||
def get_file_type(file_path: Path) -> str:
|
||
"""
|
||
根据文件扩展名判断文件类型
|
||
|
||
Returns:
|
||
'access' 或 'excel'
|
||
"""
|
||
ext = file_path.suffix.lower()
|
||
if ext in ACCESS_EXTENSIONS:
|
||
return 'access'
|
||
return 'excel'
|
||
```
|
||
|
||
**Step 3: Commit**
|
||
|
||
```bash
|
||
git add import_vba.py
|
||
git commit -m "feat: update import_vba.py main() to support both Excel and Access files"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 7: Update `.gitignore` and `CLAUDE.md` docs
|
||
|
||
**Files:**
|
||
- Modify: `.gitignore:17` (add `Access/` directory)
|
||
- Modify: `CLAUDE.md` (update docs to reflect Access support)
|
||
|
||
**Step 1: Add Access directory to `.gitignore`**
|
||
|
||
After line 17 (`Excel/`), add:
|
||
```
|
||
Access/
|
||
```
|
||
|
||
**Step 2: Update `CLAUDE.md`**
|
||
|
||
Update the project overview to mention Access support:
|
||
- Project description: mention `.accdb` alongside `.xlsm`
|
||
- Directory structure: add `Access/` source directory
|
||
- Common commands: update descriptions to mention Access files
|
||
- Platform requirements: add "Microsoft Access" as optional
|
||
|
||
**Step 3: Commit**
|
||
|
||
```bash
|
||
git add .gitignore CLAUDE.md
|
||
git commit -m "docs: update documentation for Access file support"
|
||
```
|