# ⚙️ B18｜Script Batch Office Processing — Companion Assets

> **Series**: AI Office Practice: From Beginner to Expert
> **Article**: B18 (Track 3 Automation / Level 2 Intermediate)
> **Title (CN)**: AI 写脚本批量处理 Excel / Word
> **Title (EN)**: Script Batch Office Processing

---

## 1. Who is this for? What problem does it solve?

For anyone drowning in **200 repetitive Office tasks every month**: batch renames, batch find-and-replace, batch-to-PDF, batch merge — all "mechanical, voluminous, rule-based".

The main tutorial teaches you how to **ask ChatGPT / Tongyi to write scripts** for you. This folder provides **four real, runnable PowerShell scripts** covering the four most common scenarios — **copy and run**:

| Case | What it does | Key technology |
|------|--------------|----------------|
| A | Bulk rename files | `Get-ChildItem` + `Rename-Item` (**no Office needed**) |
| B | Word batch find/replace | Word COM + `Find.Execute` (5 core arguments) |
| C | Bulk convert to PDF | Word COM `SaveAs(_, 17)` + Excel COM `ExportAsFixedFormat(0, _)` |
| D | Merge Word documents | Word COM `InsertFile` + `InsertBreak` (output to a separate folder) |

---

## 2. How to use (4-step recipe for any case)

### Step 1 — Set up the environment (one-time)

1. **Desktop Office**: must be installed locally (`WINWORD.exe` / `EXCEL.exe`). The web version does not work.
2. **PowerShell ISE or VS Code**: open *PowerShell ISE* from the Start menu.
3. **Loosen the execution policy** (first time only):
   ```powershell
   Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
   ```
   After this, `.ps1` files run without being blocked.

### Step 2 — Prepare test files

Open `SampleFiles_en.txt` in this folder and create some test files in `C:\Test\…` sub-folders (use any names that match the parameters).

### Step 3 — Edit the path parameters

Each `.ps1` starts with a `param(...)` block. Change the folder paths to your own (e.g. `-Folder "C:\Test\Contracts"`). Editing in PowerShell ISE is the easiest.

### Step 4 — Press F5 (or run from the command line)

```powershell
# In the folder where the .ps1 lives:
.\案例A_批量重命名_en.ps1 -Folder "C:\Test\Rename_Before" -OldPart "SalesReport" -NewPart "2024Sales"
# ↑ Preview mode by default. Add -Apply to actually rename.
```

Each script prints `[Done] filename` or `[Fail] reason` for every file.

---

## 3. File list

| File | Type | Case | Safety level |
|------|------|------|--------------|
| `B18_BatchScript_EnglishREADME.md` | Markdown | This file | — |
| `B18_批量脚本_中文README.md` | Markdown | Chinese README | — |
| `案例A_批量重命名_en.ps1` | PowerShell | A: rename | 🟡 Yellow (preview by default) |
| `案例A_批量重命名_zh.ps1` | PowerShell | A: Chinese | 🟡 Yellow |
| `案例B_Word查找替换_en.ps1` | PowerShell | B: Word replace | 🟡 Yellow (modifies docs) |
| `案例B_Word查找替换_zh.ps1` | PowerShell | B: Chinese | 🟡 Yellow |
| `案例C_批量转PDF_en.ps1` | PowerShell | C: PDF | 🟡 Yellow (creates PDFs) |
| `案例C_批量转PDF_zh.ps1` | PowerShell | C: Chinese | 🟡 Yellow |
| `案例D_合并Word_en.ps1` | PowerShell | D: merge Word | 🟡 Yellow (creates merged file) |
| `案例D_合并Word_zh.ps1` | PowerShell | D: Chinese | 🟡 Yellow |
| `SampleFiles_en.txt` | Text | English test file checklist | — |
| `示例文件清单.txt` | Text | Chinese test file checklist | — |

---

## 4. Practical code (key snippets explained)

### Case A — Bulk Rename (core)

```powershell
$files = Get-ChildItem -Path $Folder -Filter $Filter -File    # 1 enumerate
foreach ($f in $files) {
    $newName = $f.Name -replace "SalesReport", "2024Sales"     # 2 compute new name
    Rename-Item -Path $f.FullName -NewName $newName            # 3 rename
    Write-Host "[Done] $($f.Name)  ->  $newName"
}
```

**3 steps**: enumerate, compute, rename. **Pure PowerShell** — runs even on a PC without Office.

### Case B — Find.Execute's 5 core arguments

```powershell
[void]$sel.Find.Execute(
    [ref]"Co., Ltd.",      # 1  FindText         text to find
    [ref]$false,           # 2  MatchCase
    [ref]$false,           # 3  MatchWildcards
    [ref]$false,           # 4  MatchSoundsLike
    [ref]$false,           # 5  MatchAllWordForms
    [ref]$true,            # 6  Forward
    [ref]1,                # 7  Wrap             1 = continue from top
    [ref]$false,           # 8  Format
    [ref]"Group Co., Ltd.",# 9  ReplaceWith
    [ref]2                 # 10 Replace          2 = replace all
)
```

`.Execute()` accepts up to 15 parameters; these 10 are the common ones. **The magic numbers are `2` (replace all) and `1` (continue search)**.

### Case C — Two different APIs for PDF export

```powershell
# Word: SaveAs(path, 17) — 17 = wdFormatPDF
[void]$doc.SaveAs([ref]$pdf, [ref]17)

# Excel: ExportAsFixedFormat(0, path) — 0 = xlTypePDF
[void]$wb.ExportAsFixedFormat(0, $pdf)
```

**Don't mix them up**: Word uses `SaveAs(_, 17)`; Excel uses `ExportAsFixedFormat(0, _)`. The numeric constants are baked into Office.

### Case D — The "output-folder self-check" trick

```powershell
# Output file MUST live outside the source folder
if ($OutputFile.StartsWith($SourceFolder.TrimEnd('\') + ...)) {
    Write-Host "[Error] Output file must not be inside the source folder." -ForegroundColor Red
    exit 1
}
```

This check is the **highlight** of Case D. Without it, a second run would re-merge the previous "merged.docx" into itself, growing exponentially.

---

## 5. FAQ / Troubleshooting

1. **Desktop Office is required.** COM objects only control the on-disk Office executables. Web Office, Office 365 cloud files, and mobile apps are all unsupported.

2. **Always `Quit()` and `ReleaseComObject` in `finally`.** Each script above does this. If you skip the cleanup, leftover `WINWORD.EXE` / `EXCEL.EXE` processes pile up and slow down your PC — check Task Manager.

3. **Quote paths with spaces or non-ASCII characters.** e.g. `"C:\My Documents\Contracts"` — without quotes, PowerShell treats the space as a command separator and errors out.

4. **`.ps1` blocked on first run?** Run `Set-ExecutionPolicy RemoteSigned -Scope CurrentUser` once. This trusts locally written scripts; remote downloads still need a signature.

5. **PDF export fails / `SaveAs(17)` errors?** Pre-2010 Office doesn't have built-in PDF export — install the *Microsoft Save as PDF* add-in, or upgrade Office.

---

## 6. Next steps

After running all four cases, you can write "one-click batch scripts for hundreds of Office files". What's next?

- **Want error handling and logs?** Read **B19 — Modular Scripts & Error Handling**: use `try/catch/finally` so a single bad file doesn't break the batch, and write every step to a log.
- **Want scheduled automation?** Read **B20 — Automation Pipelines**: wire your `.ps1` into Windows Task Scheduler to run at 2 AM daily.
- **Want to write your own?** Revisit **A3 — Prompt Engineering**: use the "Role + Task + Constraint + Output Format" template to get AI to write more reliable scripts.

---

> 📌 **Quick checklist** (verify before running):
> - [ ] Desktop Office installed (not the web version);
> - [ ] Execution policy loosened: `Set-ExecutionPolicy RemoteSigned -Scope CurrentUser`;
> - [ ] Edited the `param` paths to your own folders;
> - [ ] Case A is preview-only unless `-Apply` is added;
> - [ ] Case D's output file path is outside the source folder.