Let's fix this step by step — your error comes from a mix of incorrect string formatting, unescaped path characters, and wrong Excel external reference syntax. Here's how to get it working:

First, Understand the Root Issues #

1. Unicode Escape Error: Windows paths use backslashes (\), which Python interprets as escape characters. Mixing r (raw string) inside an f-string doesn't work like you think — you need to either use rf"" or handle path separators properly.
2. Incorrect Excel Formula Format: To reference an external file in Excel, the syntax needs to be ='File/Path/[FileName.xlsx]SheetName'!CellReference. You were missing the [FileName.xlsx] part and had extra parentheses.
3. Glob Pattern Mistake: glob.glob(".xlsx") only matches a file literally named .xlsx — you want *.xlsx to match all Excel files, and should target your input directory specifically.

Fixed Code Implementation #

import os
from glob import glob
from openpyxl import load_workbook

# Load your template workbook
template_path = os.path.join(r"C:\Users\pallist\AROTRON_OUT", "template.xlsx")
wb = load_workbook(template_path)
sheet = wb.worksheets[0]

# Get all Excel files from your input directory (adjust path if needed)
input_dir = r"C:\Users\pallist\AROTRON_IN"
files = glob(os.path.join(input_dir, "*.xlsx"))

# Iterate over each file, and populate column 14 (N column) with the reference
# Using enumerate to start filling from row 2 (skip header row)
for row_num, file_path in enumerate(files, start=2):
    # Split the full path into directory and filename (Excel needs both)
    file_dir = os.path.dirname(file_path)
    file_name = os.path.basename(file_path)
    
    # Convert backslashes to forward slashes (Excel accepts this, no escape issues)
    formatted_dir = file_dir.replace("\\", "/")
    
    # Build the valid Excel external reference formula
    # The single quotes are mandatory if your path has spaces
    formula = f"='{formatted_dir}/[{file_name}]Sheet1'!$N$2"
    
    # Assign the formula to the cell in column 14, current row
    sheet.cell(row=row_num, column=14).value = formula

# Save the modified workbook (use a new name to avoid overwriting the template)
wb.save(os.path.join(r"C:\Users\pallist\AROTRON_OUT", "filled_template.xlsx"))

Key Explanations for Using the Iterator in the Loop #

• We use enumerate(files, start=2) to track which row to write to — start=2 assumes your template has a header row in row 1. The row_num variable gives us the current row number to target in column 14.
• The file_path variable (your original f) is used to extract the directory and filename needed for the Excel formula. By splitting these parts, we ensure the formula follows Excel's required format.
• Using os.path functions (dirname, basename, join) makes your code more cross-platform and avoids manual path formatting errors.

Why Your Original Code Failed #

• You tried to mix r inside an f-string (f"=r'C:\Users...'), which doesn't work — raw string prefixes need to be outside the f-string (like rf"..."), but even then, Excel's formula syntax requires the [FileName.xlsx] wrapper around the filename.
• Your glob pattern was incorrect, so you likely weren't matching any files in the first place.
• The extra closing parenthesis at the end of your formula string was causing unnecessary syntax issues.

内容的提问来源于stack exchange，提问作者Θοδωρής Πάλλης