如何使用PowerShell将Excel工作簿文件名复制到指定单元格

需求说明 #

• 遍历指定目录下的所有Excel文件
• 打开每个工作簿，提取不带扩展名的工作簿名称
• 将名称写入工作簿内名为Jpl的工作表的G6单元格
• 操作完成后保存并关闭工作簿，最终退出Excel进程

原代码问题分析 #

原代码没有把获取到的工作簿名称赋值给目标单元格，错误使用了PasteSpecial方法，且打开工作簿时第三个参数设为$true是只读模式，会导致无法保存修改。

修正后可运行代码 #

# 获取指定目录下的Excel文件
$Files=Get-ChildItem -Path "D:\MyExcelfiles\GR-E" -Filter *.xlsx
# 实例化Excel应用对象
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $False
$xl.DisplayAlerts = $False

foreach($File in $Files){
    # 获取不带扩展名的工作簿名称
    $baseName = [System.IO.Path]::GetFileNameWithoutExtension($file.FullName)
    Write-Host "正在处理文件：$baseName"
    # 打开工作簿，第三个参数设为$false关闭只读模式
    $wb1 = $xl.Workbooks.Open($file.FullName, $null, $false)
    # 定位到Jpl工作表
    $worksheet = $wb1.Sheets.Item("Jpl")
    # 给G6单元格赋值为工作簿名称
    $worksheet.Range("G6").Value = $baseName
    # 保存修改并关闭工作簿
    $wb1.Save()
    $wb1.Close()
}
# 退出Excel进程
$xl.Quit()
# 释放COM对象，避免后台残留Excel进程
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
Remove-Variable xl

关键改动说明 #

• 给Get-ChildItem增加-Filter *.xlsx参数，仅处理Excel文件，避免读取其他格式文件报错
• 打开工作簿时第三个参数修改为$false，允许编辑保存
• 去掉错误的PasteSpecial调用，直接给Jpl工作表G6单元格的Value属性赋值
• 新增COM对象释放逻辑，避免后台残留Excel进程占用系统资源

内容的提问来源于stack exchange，提问作者RMat