Spaces:
Sleeping
Sleeping
| """Update VBA modules inside Template.xlsm workbooks.""" | |
| from __future__ import annotations | |
| import sys | |
| from pathlib import Path | |
| import win32com.client | |
| ROOT = Path(__file__).resolve().parents[1] | |
| MODULES = { | |
| "DuplicateImageReview": ROOT / "DuplicateImageReview.bas", | |
| "ClearSheet": ROOT / "ClearSheet.bas", | |
| "Module1": ROOT / "Module1.bas", | |
| "ClearClearedData": ROOT / "ClearClearedData.bas", | |
| "AmazonUrlCleaner": ROOT / "AmazonUrlCleaner.bas", | |
| } | |
| SHEET_MODULES = { | |
| "Sheet2": ROOT / "ManualSheet.cls", | |
| "Sheet3": ROOT / "WriteBufferSheet.cls", | |
| } | |
| def read_vba(path: Path) -> str: | |
| lines = path.read_text(encoding="utf-8").splitlines() | |
| if lines and lines[0].startswith("Attribute VB_Name"): | |
| lines = lines[1:] | |
| return "\n".join(lines) + "\n" | |
| def replace_module_code(component, code: str) -> None: | |
| code_module = component.CodeModule | |
| count = code_module.CountOfLines | |
| if count: | |
| code_module.DeleteLines(1, count) | |
| code_module.AddFromString(code) | |
| def ensure_clear_data_button(wb) -> None: | |
| """Create or refresh the Clear Data form button on Cleared Data.""" | |
| try: | |
| ws = wb.Worksheets("Cleared Data") | |
| except Exception: | |
| print("Cleared Data sheet not found; skip button") | |
| return | |
| # Remove prior button with same name / action | |
| for i in range(ws.Buttons.Count, 0, -1): | |
| btn = ws.Buttons(i) | |
| try: | |
| if btn.Name == "btnClearClearedData" or btn.OnAction.endswith( | |
| "ClearClearedDataSheet" | |
| ): | |
| btn.Delete() | |
| except Exception: | |
| pass | |
| left = ws.Range("L1").Left | |
| top = ws.Range("L1").Top | |
| btn = ws.Buttons.Add(left, top, 100, 28) | |
| btn.Name = "btnClearClearedData" | |
| btn.OnAction = "ClearClearedDataSheet" | |
| btn.Characters.Text = "Clear Data" | |
| print("Clear Data button placed on Cleared Data") | |
| def update_workbook(path: Path) -> None: | |
| excel = win32com.client.Dispatch("Excel.Application") | |
| excel.Visible = False | |
| excel.DisplayAlerts = False | |
| wb = None | |
| try: | |
| wb = excel.Workbooks.Open(str(path)) | |
| try: | |
| _ = wb.VBProject.Name | |
| except Exception as exc: | |
| raise RuntimeError( | |
| "Excel blocked VBA access. Enable " | |
| "'Trust access to the VBA project object model' in " | |
| "File > Options > Trust Center > Macro Settings, then rerun." | |
| ) from exc | |
| existing = {c.Name for c in wb.VBProject.VBComponents} | |
| for name, file_path in MODULES.items(): | |
| if not file_path.exists(): | |
| print(f"Skipping missing module file: {file_path}") | |
| continue | |
| if name in existing: | |
| component = wb.VBProject.VBComponents(name) | |
| replace_module_code(component, read_vba(file_path)) | |
| else: | |
| component = wb.VBProject.VBComponents.Import(str(file_path)) | |
| print(f"Imported {name}") | |
| for name, file_path in SHEET_MODULES.items(): | |
| if not file_path.exists(): | |
| continue | |
| if name not in existing: | |
| continue | |
| component = wb.VBProject.VBComponents(name) | |
| replace_module_code(component, read_vba(file_path)) | |
| ensure_clear_data_button(wb) | |
| try: | |
| wb.Worksheets("Manual").Activate | |
| print("Active sheet set to Manual") | |
| except Exception as exc: | |
| print(f"Could not activate Manual sheet: {exc}") | |
| wb.Save() | |
| print(f"Updated {path}") | |
| finally: | |
| if wb is not None: | |
| wb.Close(SaveChanges=False) | |
| excel.Quit() | |
| def main(argv: list[str]) -> int: | |
| targets = [Path(arg) for arg in argv[1:]] or [ | |
| ROOT / "Template.xlsm", | |
| Path(r"F:\Vishakha\Template.xlsm"), | |
| ] | |
| for target in targets: | |
| if not target.exists(): | |
| print(f"Skipping missing file: {target}") | |
| continue | |
| update_workbook(target) | |
| return 0 | |
| if __name__ == "__main__": | |
| raise SystemExit(main(sys.argv)) | |