更新时间:2023-11-04 14:16:52
带引号的字符串中的引号需要加倍.
$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.