[Automation]Script Batch Office Processing

Summary: Use AI to write a PowerShell script and finish repetitive work across hundreds of files with a single run.

1. Pain Points

You've almost certainly been worn down by jobs like these:

At month-end you must submit 200 Excel reports, with filenames like "销售报表_01.xlsx, 销售报表_02.xlsx…". Your boss wants them all uniformly renamed to "2024销售_01.xlsx", so you can only right-click and rename them one by one until your eyes spin;
The company name changed from "XX有限公司" to "XX集团有限公司", and dozens of Word contracts all need the change. You open each document and press Ctrl + H to replace them one at a time;
You need to merge 50 Word weekly reports into one to send to your manager, or convert 30 Excel / Word files to PDF for archiving — all done by manually clicking "Save As" until your mouse is practically smoking.

The common thread of these jobs is perfectly clear: the rules are identical, the volume is huge, and it's pure repetition. Done by hand it's not only slow but also easy to miss things and make mistakes; whereas a script excels at "following the same rule and repeating it a hundred times."

The catch is: you're not a programmer, so writing code from scratch is too high a bar. This article teaches you a shortcut — let ChatGPT write the PowerShell script for you. You just tell it clearly "what I want to do," it spits out the code, and you copy and run it.

2. What You'll Achieve

After reading and following along, you'll walk away with:

A clear grasp of two new terms: PowerShell and COM object (explained in plain language before the hands-on part);
A reusable prompt template for "asking ChatGPT for a script" that you can apply to any batch task later;
Four real, runnable PowerShell scripts: batch renaming, batch Word find-and-replace, batch PDF conversion, and batch Word merging;
A pitfall checklist so you know where things are most likely to go wrong and how to recover.

This article is L2 (Intermediate). You don't need to know how to write code, but you should have opened a "command prompt / terminal" at least once, and your computer should have the desktop version of Office installed (we'll explain why shortly).

3. Hands-on Cases

3.0 Two Terms to Know First (otherwise the code won't make sense)

PowerShell = the command-line tool built into Windows that can command your computer to do jobs in bulk. Think of it as an "upgraded command prompt," purpose-built for writing scripts that run tasks automatically. It ships with Win10/Win11, so no separate install is needed.
COM object = a kind of "remote-control interface" in Windows. Through it, PowerShell can "open" the Word or Excel already installed on your computer, just like a person would, and then simulate clicking menus. In the article you'll see New-Object -ComObject Word.Application, which means "create a remote-control interface for Word, ready to drive Word to do the work."

3.1 Preparation (Two Steps)

Step 1: Confirm you have the desktop version of Office installed. A COM object can only remote-control "the Office desktop program installed on your computer" to work. The web version of Office (the one in the browser) and mobile apps won't do. How to check? Open the Start menu; if you can find the "Word / Excel" desktop icons and open .docx / .xlsx files normally, then it's installed — OK.

Step 2: Open PowerShell ISE. "PowerShell ISE" is the script editor that ships with PowerShell — you write code on the left and run it up top, the most beginner-friendly option. How to open it: click the Start menu, search directly for PowerShell ISE, and click to open it. All the scripts below are pasted into its script pane and run with F5.

Tip: When your script uses Chinese paths or filenames, PowerShell ISE saves files in UTF-8 by default, which is generally fine; if you save the .ps1 with Notepad instead, remember to choose "UTF-8" encoding when saving, otherwise Chinese characters will turn into garbled text.

3.2 Case A: Use AI to Write a Script for Batch Renaming

What you tell ChatGPT (prompt):

"Write me a PowerShell script: in the C:\报表 folder, replace '销售报表' with '2024销售' in every filename — for example, 销售报表_01.xlsx becomes 2024销售_01.xlsx. Print one confirmation line after each rename. Use only built-in PowerShell commands; don't touch Office."

The script ChatGPT gives you (real and runnable):

powershell
# Batch rename: replace "销售报表" with "2024销售" in filenames (Chinese name fragments kept for the real-world case)
$folder = "C:\报表"   # hardcoded Chinese folder name, kept as-is

Get-ChildItem -Path $folder -Filter "销售报表_*.xlsx" | ForEach-Object {
    # Use -replace to swap the old name fragment for the new one
    $newName = $_.Name -replace "销售报表", "2024销售"
    Rename-Item -Path $_.FullName -NewName $newName
    Write-Host "Renamed: $($_.Name)  ->  $newName"
}

Write-Host "All done!"

How to run: Paste the code above into PowerShell ISE, change $folder to your own folder, and press F5. Key point: This step uses only built-in PowerShell commands (Get-ChildItem to list files, Rename-Item to rename), and doesn't touch Office, so it runs even without Office installed.

3.3 Case B: Batch Find-and-Replace in Word

What you tell ChatGPT (prompt):

"Write a PowerShell script: use a COM object to open every .docx in the C:\合同 folder, replace '有限公司' with '集团有限公司' throughout each document, then save and close it. Print one line after each file is processed. When the script finishes, quit Word and release the COM objects."

The script ChatGPT gives you (real and runnable):

powershell
# Batch Word find-and-replace: 有限公司 -> 集团有限公司 (the replaced strings themselves stay Chinese)
$folder = "C:\合同"   # hardcoded Chinese folder name, kept as-is

# Create Word's remote-control interface (COM object); run hidden, no window pops up
$word = New-Object -ComObject Word.Application
$word.Visible = $false
$word.DisplayAlerts = $false   # Suppress dialogs like "Do you want to save?"

Get-ChildItem -Path $folder -Filter "*.docx" | ForEach-Object {
    $doc = $word.Documents.Open($_.FullName)
    $sel = $word.Selection

    $sel.Find.ClearFormatting()
    $sel.Find.Replacement.ClearFormatting()

    # Find.Execute parameters, left to right:
    # 1st = find text, 7th = search forward(true), 8th = Wrap(1), 10th = replacement text, 11th = 2 (replace all)
    [void]$sel.Find.Execute(
        [ref]"有限公司", [ref]$false, [ref]$false, [ref]$false, [ref]$false,
        [ref]$false,      [ref]$true,  [ref]1,      [ref]$false, [ref]"集团有限公司", [ref]2)

    $doc.Save()
    [void]$doc.Close()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($doc) | Out-Null  # Release the current document's COM reference
    Write-Host "Processed: $($_.Name)"
}

# Quit Word and release the COM object (important - see Pitfall Guide item 2)
[void]$word.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($word) | Out-Null
Remove-Variable word

Write-Host "All done!"

How to run: Paste into PowerShell ISE, change $folder, and press F5. During the run you won't see the Word window (because Visible = $false) — it works quietly in the background. Key point: The 11th parameter of Find.Execute, 2, means "replace all" (called wdReplaceAll in Word); the 8th parameter, 1, means "after reaching the end, go back to the top and continue" (wdFindContinue). These two numbers are Office's internal constants — just remember them.

3.4 Case C: Batch Convert Word / Excel to PDF

What you tell ChatGPT (prompt):

"Write two PowerShell scripts: the first converts every Word (.docx) file in C:\文档 into a same-named PDF; the second converts every Excel (.xlsx) file in C:\表格 into a same-named PDF. Use a COM object for both, run hidden in the background, and quit and release the objects when done."

The script ChatGPT gives you (real and runnable):

powershell
# ===== Part 1: batch-convert Word files to PDF =====
$wordFolder = "C:\文档"   # hardcoded Chinese folder name, kept as-is
$word = New-Object -ComObject Word.Application
$word.Visible = $false
$word.DisplayAlerts = $false   # Suppress dialogs like "Do you want to save?"

Get-ChildItem -Path $wordFolder -Filter "*.docx" | ForEach-Object {
    $doc  = $word.Documents.Open($_.FullName)
    $pdf  = $_.FullName -replace "\.docx$", ".pdf"
    # SaveAs's 2nd parameter, 17 = wdFormatPDF (Word's built-in PDF export format)
    [void]$doc.SaveAs([ref]$pdf, [ref]17)
    [void]$doc.Close()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($doc) | Out-Null  # Release the current document's COM reference
    Write-Host "Converted to PDF: $pdf"
}
[void]$word.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($word) | Out-Null
Remove-Variable word

# ===== Part 2: batch-convert Excel files to PDF =====
$excelFolder = "C:\表格"   # hardcoded Chinese folder name, kept as-is
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false   # Suppress dialogs like "Do you want to save?"

Get-ChildItem -Path $excelFolder -Filter "*.xlsx" | ForEach-Object {
    $wb   = $excel.Workbooks.Open($_.FullName)
    $pdf  = $_.FullName -replace "\.xlsx$", ".pdf"
    # ExportAsFixedFormat's 1st parameter, 0 = xlTypePDF (export as PDF)
    [void]$wb.ExportAsFixedFormat(0, $pdf)
    [void]$wb.Close()
    Write-Host "Converted to PDF: $pdf"
}
[void]$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

Write-Host "All done!"

How to run: Change $wordFolder and $excelFolder to your own directories, and press F5. Key point: Word uses SaveAs(path, 17) and Excel uses ExportAsFixedFormat(0, path) — the two commands for PDF conversion are different, so don't mix them up. The numbers 17 and 0 are both internal Office format constants.

Robustness tip: To keep one file failing to convert from affecting the others, wrap each Get-ChildItem ... | ForEach-Object { ... } block in a try/catch (see B19《模块化脚本与错误处理》 for details) — log the bad one and convert the good ones as usual.

3.5 Case D: Merge Dozens of Word Weekly Reports into One

What you tell ChatGPT (prompt):

"Write a PowerShell script: use a COM object to merge all the .docx files in the C:\周报 folder, in filename order, into one new document C:\周报_合并\合并周报.docx (output to a different directory), inserting a page break between documents. Run in the background; quit and release the objects when done."

The script ChatGPT gives you (real and runnable):

powershell
# Batch-merge Word docs: combine several documents into one, with a page break between them
$folder = "C:\周报"   # hardcoded Chinese folder name, kept as-is
# Output to a different directory so a second run doesn't embed last time's 合并周报.docx again
$out = "C:\周报_合并\合并周报.docx"
# Make sure the output directory exists first (Split-Path gives the path before the file name)
New-Item -ItemType Directory -Force -Path (Split-Path $out) | Out-Null

$word = New-Object -ComObject Word.Application
$word.Visible = $false
$word.DisplayAlerts = $false   # Suppress dialogs like "Do you want to save?"

$merged = $word.Documents.Add()   # create a new blank document to act as the container
$sel    = $word.Selection

# Sort-Object Name orders files by dictionary order (name source files with equal-length numbers like 01, 02..., otherwise 10 sorts before 2)
Get-ChildItem -Path $folder -Filter "*.docx" | Sort-Object Name | ForEach-Object {
    # InsertFile inserts the current document's content at the cursor
    [void]$sel.InsertFile($_.FullName)
    # InsertBreak: first argument 7 = wdPageBreak, so each file starts on a new page
    [void]$sel.InsertBreak(7)
    Write-Host "Merged: $($_.Name)"
}

[void]$merged.SaveAs([ref]$out)
[void]$merged.Close()
[void]$word.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($word) | Out-Null
Remove-Variable word

Write-Host "Merge complete, file saved at: $out"

How to run: Change $folder (the source weekly-report directory) and press F5. The merged result outputs to C:\周报_合并\合并周报.docx (separate from the source directory, so a second run won't embed last time's result again). Key point: The Sort-Object Name step is important — it orders files by filename in dictionary order; it's recommended to use equal-length numbering like 01, 02… for source files, otherwise 10 will sort before 2 and the weekly reports will be out of order, which will give your manager a headache.

4. How It Works (Quick Recap)

At the end of the day, PowerShell itself doesn't "understand" Word or Excel. It simply uses the COM object remote-control interface to quietly launch an invisible Office program in the background on your computer, then simulates the series of mouse actions you'd normally do — "open the file → click find-and-replace → click save." The value of AI (like ChatGPT) here is translating your plain-language request "help me change the company name in dozens of contracts" into the script above with its [ref] and numeric constants. You don't need to memorize these parameters — just remember the pattern "let AI write it, you copy and run it" and that's enough.

5. Pitfall Guide

You must install the desktop version of Office. A COM object can only remote-control the Office desktop program installed on your computer (Word/Excel's .exe). The web version of Office, files in Office 365 cloud storage, and mobile apps can't connect, and the script will throw errors like "COM object not found."
Always Quit() and release the COM object when the script finishes. Every case above ends with $word.Quit() and ReleaseComObject. If you skip this step, the background Word/Excel processes won't close, and they'll pile up and slow down your computer — you'll see a bunch of WINWORD.EXE / EXCEL.EXE in Task Manager. Make it a habit: open as many as you close.
Always quote paths containing Chinese characters or spaces. For example "C:\我的文档\合同" must be wrapped in double quotes, otherwise PowerShell will treat the space as a command separator and error out immediately. You can use backslashes \ or forward slashes / for paths, but keep it consistent within the same script.
Your first .ps1 run may be blocked by Windows. If double-clicking or running it shows "cannot be loaded because running scripts is disabled on this system," first open PowerShell and run: Set-ExecutionPolicy RemoteSigned -Scope CurrentUser (meaning: allow running scripts written on this machine). This is Windows' security default — change it once and you're set.
PDF conversion relies on Office's built-in export feature. SaveAs(…, 17) and ExportAsFixedFormat(0, …) use the PDF export built into Word 2010 / Excel 2010 and later. If you're on the older Office 2007 (without the PDF add-in), PDF conversion will fail — just upgrade Office.

6. Further Reading

By now, you can use AI to write PowerShell scripts that finish repetitive Office chores with a single run — that already solves 80% of the "batch pain."

Want to build the basics first? If "command line / scripts" still feels a bit vague, it's recommended to revisit B17《第一条命令》 from L1, which takes you from the most foolproof first step to build the feel of "letting the computer run commands for me" — coming back to this article afterward will be much easier.
Want to go one level up? Right now you still have to manually press F5 to run the script. The next step is L3's "Task Scheduler to run scripts automatically on a schedule" — hook your finished .ps1 to a timer, such as automatically converting the day's reports to PDF at 2 a.m. every day, so you truly "wake up to results."
Find PowerShell too hardcore? You can also branch off to the macro (宏) route: the built-in "Record Macro" in Excel / Word can record your mouse actions as code, with an even lower barrier than PowerShell, suited to people who only do repetitive operations within a single file.

🛠️ Script Batch Office Processing

[Automation]Script Batch Office Processing

1. Pain Points

2. What You'll Achieve

3. Hands-on Cases

3.0 Two Terms to Know First (otherwise the code won't make sense)

3.1 Preparation (Two Steps)

3.2 Case A: Use AI to Write a Script for Batch Renaming

3.3 Case B: Batch Find-and-Replace in Word

3.4 Case C: Batch Convert Word / Excel to PDF

3.5 Case D: Merge Dozens of Word Weekly Reports into One

4. How It Works (Quick Recap)

5. Pitfall Guide

6. Further Reading

📎 Downloads

Script Batch Office Processing