如何使用 PowerShell 插入 Excel 公式? [英] How to Insert Excel Formula using PowerShell?

问题描述

公式 =LEFT(AB4,FIND(" ",AB5)-1 在 Excel 中完美运行，但似乎在 PowerShell 中导致错误，我收到此错误:

<前>HRESULT 异常:0x800A03EC在 C:ScriptsExcel_NUID2.ps1:21 字符:1+ $worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo : OperationStopped: (:) [], COMException+ FullQualifiedErrorId : System.Runtime.InteropServices.COMException

我的 PowerShell 脚本代码；

#打开工作簿#$excel = new-object -comobject Excel.Application$excel.visible = $false$工作簿=$excel.workbooks.open("c:UsersJackdocumentsNUID_Status_Report.xlsx")$worksheet = $workbook.Worksheets.Item(1)$rows = $worksheet.range("A1").currentregion.rows.count###设置过滤器###$headerRange = $worksheet.Range("a4","aj4")$headerRange.AutoFilter() |出空#### 修剪密码到期日期名称###$worksheet.range("AH4").formula = "缩短的到期日期"[void]$worksheet.Cells.Item(1,1).select()$excel.visible = $true#### 修剪密码到期日期公式###$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"[void]$worksheet.Cells.Item(1,1).select()$excel.visible = $true

解决方案

带引号的字符串中的引号需要加倍.

$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND("" "",AB5)-1)"'你也可以用 CHAR 函数去掉内引号$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4, FIND(CHAR(32), AB5)-1)"

ASCII 字符 32 是一个空格.我还添加了一个括号来制作一个合法的公式.

The formula =LEFT(AB4,FIND(" ",AB5)-1 works perfectly in Excel, but seems to be causing errors in PowerShell where I get this error:

Exception from HRESULT: 0x800A03EC
At C:ScriptsExcel_NUID2.ps1:21 char:1
+ $worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException 

My PowerShell Script Code;

#Open Up the Workbook#
$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$workbook = 
$excel.workbooks.open("c:UsersJackdocumentsNUID_Status_Report.xlsx") 
$worksheet = $workbook.Worksheets.Item(1)

$rows = $worksheet.range("A1").currentregion.rows.count

### Set up a filter ###
$headerRange = $worksheet.Range("a4","aj4")
$headerRange.AutoFilter() | Out-Null

#### Trims Password Expiration Date Name ###

$worksheet.range("AH4").formula = "Shortened Expiration Date"
[void]$worksheet.Cells.Item(1,1).select()
$excel.visible = $true

#### Trims Password Expiration Date Formula ###

$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"
[void]$worksheet.Cells.Item(1,1).select()
$excel.visible = $true

解决方案

Quotes within a quoted string need to be doubled-up.

$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND("" "",AB5)-1)"
'you can also get rid of the inside quotes with the CHAR function
$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4, FIND(CHAR(32), AB5)-1)"

ASCII character 32 is a space. I've also added a bracket to make a legal formula.

这篇关于如何使用 PowerShell 插入 Excel 公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！