Excel 里改了单元格却不生效？——先搞清"这个格子归谁管"（附把时间戳当版本号的翻车复盘）

import win32com.client as w32

app = w32.DispatchEx("Excel.Application")
app.Visible = False
app.EnableEvents = False
wb = app.Workbooks.Open(r"D:\work\app.xlsm", UpdateLinks=0)
ws = wb.Worksheets("Customers")

PWD = "your-password"          # worksheet protection password

if ws.ProtectContents:
    ws.Unprotect(PWD)

ws.Range("J2").Value = "Uploaded"
rng = ws.Range("A2:P2")
rng.Font.Name = "DengXian"
rng.Font.Size = 11
rng.Font.Bold = True
rng.Interior.Color = 12611584          # RGB(0,112,192)
rng.HorizontalAlignment = -4108        # center

ws.Protect(Password=PWD, DrawingObjects=True, Contents=True,
           Scenarios=True, UserInterfaceOnly=True, AllowFormattingColumns=True)
wb.Save(); wb.Close(False); app.Quit()

$ rg -n "Cells$2, 10$|Cells$2,10$" Main.bas
2870:    ws.Cells(2, 10).Value = "[OLD TEXT]"

Public Sub EnsureCustomerHeaders()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customers")
    ' ... more setup ...
    ws.Cells(2, 1).Value = "RecordID"
    ws.Cells(2, 2).Value = "Company"
    ' ... more header cells ...
    ws.Cells(2, 10).Value = "[OLD TEXT]"     ' culprit: rewrites the old value
    ' ... more header cells ...
End Sub

Ensure*     Init*     Build*     Rebuild*     Reset*     Refresh*Layout

' ===== Unified header style: reapplied on every open, so Ensure* cannot revert it =====
Public Sub ApplyHeaderRules()
    On Error Resume Next
    Dim pw As String, ws As Worksheet, wasProt As Boolean
    pw = GetAdminPwd()
    Application.EnableEvents = False

    Set ws = Nothing
    Set ws = ThisWorkbook.Worksheets("Customers")
    If Not ws Is Nothing Then
        wasProt = ws.ProtectContents
        If wasProt Then ws.Unprotect pw
        ws.Cells(2, 10).Value = "Uploaded"      ' force the new text back
        StyleHeader ws, "A2:P2"
        ws.Range("D:D").WrapText = True
        ws.Range("E:E").WrapText = True
        ws.Range("F:F").WrapText = True
        If wasProt Then ws.Protect Password:=pw, DrawingObjects:=True, Contents:=True, _
                              Scenarios:=True, UserInterfaceOnly:=True, AllowFormattingColumns:=True
    End If
    ' ... same block for the other two sheets ...
    Application.EnableEvents = True
End Sub

Private Sub StyleHeader(ByVal ws As Worksheet, ByVal addr As String)
    On Error Resume Next
    Dim r As Range
    Set r = ws.Range(addr)
    r.Font.Name = "DengXian"
    r.Font.Size = 11
    r.Font.Bold = True
    r.Font.Color = RGB(255, 255, 255)
    r.Interior.Color = RGB(0, 112, 192)
    r.HorizontalAlignment = -4108
End Sub

Public Sub EnsureCustomerHeaders()
    ' ... existing body ...
    ApplyHeaderRules        ' inserted right before End Sub
End Sub

cm = wb.VBProject.VBComponents("Main").CodeModule

def endsub_line(lines, start_idx):
    for i in range(start_idx, len(lines)):
        if lines[i].strip() == "End Sub":
            return i
    return -1

for proc in ("Public Sub EnsureCustomerHeaders()", "Public Sub EnsureSheetFormats()"):
    lines = cm.Lines(1, cm.CountOfLines).split("\r\n")
    pi = [i for i, l in enumerate(lines) if l.strip() == proc]
    if not pi:
        print("host not found:", proc); continue
    ei = endsub_line(lines, pi[0])
    body = lines[pi[0]:ei]
    if any("ApplyHeaderRules" in l and not l.strip().startswith("'") for l in body):
        print("already has the call, skip:", proc); continue
    cm.InsertLines(ei + 1, "    ApplyHeaderRules")
    print("inserted call at end of %s" % proc)

ws.Range("J2").Value = "[OLD TEXT]"                       # 1) revert on purpose
xl.Run("'app.xlsm'!EnsureCustomerHeaders")                # 2) run the rebuild proc
print(ws.Range("J2").Value)                               # 3) expect: Uploaded

reset to old -> Run EnsureCustomerHeaders   : J2=Uploaded   OK
reset to old -> Run ApplyHeaderRules        : J2=Uploaded   OK
reset to old -> Run EnsureSheetFormats      : J2=Uploaded   OK

$ rg -n "Cells$7, 3$" Main.bas
259:    ws.Cells(7, 3).NumberFormat = "@"
260:    ws.Cells(7, 3).Value = st                    ' writes a timestamp
926:    ws.Cells(7, 3).Value = MasterVersion(mp)     ' master version timestamp
927:
5279:   ws.Cells(7, 3).Value = MasterVersion(mp)     ' same as above
5352:   vK = Trim$(CStr(ws.Cells(7, 3).Value))       ' the only reader

Public Const APP_VER As String = "3.8"        ' the version lives here, not in any cell

If fpK = "" Then                                  ' no sync tag yet (first run)
    mv = MasterVersion(mp)                        ' real master version (timestamp)
    vK = Trim$(CStr(ws.Cells(7, 3).Value))        ' master version recorded locally
    If mv [NE] "" Then        ' [NE] 对应源码里的 <>（不等运算符）
        If vK = mv Then
            WriteSyncTag fpM, tM, szM             ' equal -> already latest, skip
            Exit Sub
        End If
    End If
End If
' not equal -> falls through and triggers a full-file overwrite

ws.Range("C7").NumberFormat = "@"
ws.Range("C7").Value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

import re

def cell_audit(bas_text, row, col):
    """Find who writes and who reads a given cell in the VBA source."""
    pats = [
        re.compile(r'Cells$\s*%d\s*,\s*%d\s*$' % (row, col)),
        re.compile(r'Range$\s*"[A-Z]*%d"\s*$' % row),      # fallback: scan by row
    ]
    writers, readers = [], []
    for i, line in enumerate(bas_text.splitlines(), 1):
        if not any(p.search(line) for p in pats):
            continue
        s = line.strip()
        if s.startswith("'"):
            continue
        if re.search(r'Cells$\s*%d\s*,\s*%d\s*$\s*\.?\w*\s*=' % (row, col), line):
            writers.append((i, s))
        else:
            readers.append((i, s))
    return writers, readers

writers, readers = cell_audit(open("Main.bas", encoding="utf-8").read(), 7, 3)
print("writers:", writers)
print("readers:", readers)

writers: [(260, 'ws.Cells(7, 3).Value = st'), (927, 'ws.Cells(7, 3).Value = MasterVersion(mp)'), (5280, 'ws.Cells(7, 3).Value = MasterVersion(mp)')]
readers: [(5352, 'vK = Trim$(CStr(ws.Cells(7, 3).Value))')]