Spaces:
Sleeping
Sleeping
File size: 4,096 Bytes
38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 38fdd3d 0dc6fb4 b084340 38fdd3d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | """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))
|